Database maintenance for SharePoint 2010 Products

 

Summary:  Learn how to maintain the databases that host data and configuration settings for Microsoft SharePoint 2010 Products. Read guidelines and study examples of the recommended database maintenance strategies and tasks.

Applies to: Microsoft SharePoint Server 2010, Microsoft SharePoint Foundation 2010

Authors: Bill Baer and Bryan Porter

Technical Reviewer: Paul S. Randal (SQLskills.com)

Contents

• Introduction

• Using Database Console Command (DBCC) CHECKDB to check for consistency errors

• About DBCC CHECKDB

• DBCC CHECKDB and Performance

• Measure and reduce index fragmentation

• Online vs. offline index rebuilds

• Measure fragmentation in a SQL Server 2008 or 2005 database (sys.dm_db_index_physical_stats)

• Reducing fragmentation for a database

• Reducing fragmentation for a specific table and its indexes

• Fine tuning index performance by setting fill factor

• Shrinking data files

• Creating SQL Server 2008 maintenance plans

• Conclusion

Introduction

Routine database maintenance is essential for the smooth operation of Microsoft SharePoint 2010 databases.

The recommended maintenance tasks for SharePoint 2010 databases include the following:

• Check database integrity.

• Defragment indexes by reorganizing them or rebuilding them.

• Set the fill factor for a server.

Although previous versions of SharePoint Products and Technologies required manual intervention to perform index defragmentation and statistics maintenance, several SharePoint Health Analyzer rules automate this process in SharePoint 2010. These rules evaluate the health of database indexes and statistics daily, and automatically address those items for the following databases:

• Configuration databases

• Content databases

• User Profile Service Application Profile databases

• User Profile Service Application Social databases

• Web Analytics Service Application Reporting databases

• Web Analytics Service Application Staging databases

• Word Automation Services databases

You can perform database maintenance tasks by running Transact-SQL commands, or by running the Database Maintenance Wizard. This article describes the Transact-SQL commands that you can use, and then explains how to create database maintenance plans by using the Microsoft SQL Server Database Maintenance Wizard. (The detailed examples are for Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2005.)

Using Database Console Command (DBCC) CHECKDB to check for consistency errors

Start your routine maintenance operations with consistency checks to ensure that your data and indexes are not corrupted. You can use the Database Console Command (DBCC) CHECKDB statement to perform an internal consistency check of the data and index pages.

Most database consistency problems result from I/O subsystem errors. However, other factors and events can affect database consistency too; for example, an improper shutdown of a database server or a drive failure. Noticeable performance and availability issues can sometimes be symptoms of underlying database consistency problems. Perform database consistency checks at least once per week on your SharePoint 2010 databases, and when events such as database server or I/O subsystem failures occur.

About DBCC CHECKDB

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• Runs the equivalent of DBCC CHECKALLOC to verify the allocation structures in the database.

• Runs the equivalent of DBCC CHECKTABLE on every table and view in the database to verify their logical and physical integrity.

• Runs the equivalent of DBCC CHECKCATALOG on the database to verify the consistency of the metadata in the database.

We recommend that you run DBCC CHECKDB instead of the individual operations (DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG commands) because DBCC CHECKDB identifies the widest range of possible errors and is therefore safer to run in a production environment.

DBCC CHECKDB uses a lot of memory, I/O, and CPU resources. Instead of running DBCC CHECKDB on your production system, you can run it on a restored backup of your SharePoint databases on a different server, and thereby offload the consistency-checking workload from the production system.

We recommend that you first run DBCC CHECKDB, and then, if it reveals errors, restore the affected database by using your most recent backups.

The following is sample output from DBCC CHECKDB.

DBCC results for 'Contoso_Content_1'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 2663 rows in 21 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 309 rows in 4 pages for object "sys.sysrowsets".

...more

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Contoso_Content_1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

For more information about how to use DBCC CHECKDB with SQL Server 2008, see DBCC CHECKDB (Transact-SQL).

DBCC CHECKDB and performance

We recommend that you run consistency checks during non-production hours because DBCC CHECKDB is extremely resource-intensive (I/O, CPU, memory, and tempdb space). There is a common misconception that DBCC CHECKDB acquires blocking locks, but that has not been true since before SQL Server 2000. For more information, see A SQL Server DBA myth a day: (2/30) DBCC CHECKDB causes blocking.

You might find that running DBCC CHECKDB uses too many resources for your production system. In that case, do not attempt to run consistency checks one table at a time. The best ways to reduce the overhead of an integrity check on the production system is to use one of the following options:

• Use the WITH PHYSICAL_ONLY option to reduce the CPU and memory usage.

• Restore a database backup on a separate SQL Server and run consistency checks on the restored copy of the database.

For more information about these options, see the blog entry from Paul S. Randal, CHECKDB From Every Angle: Consistency Checking Options for a VLDB.

Measure and reduce index fragmentation

Index fragmentation occurs when the logical order of pages in a table or index (as defined by the index key) differs from the physical order of the pages in the data files. It can also mean that the data density on data file pages is low, which results in wasted disk space, memory, and I/Os. Index fragmentation can be the result of many inserts, updates, or deletions to a table. The following illustrations contrast a newly built, non-fragmented index with an index that is fragmented after many inserts, updates, and deletions. The red arrow shows the physical order of the index; the black arrows show the logical ordering of the index pages.

Figure 1. Non-fragmented index (Image source: Paul S. Randal)

 

Figure 2. Fragmented Index (Image source: Paul S. Randal)

 

Because inserts, updates, and deletions are not distributed equally among the rows of the table and indexes, the fullness (or data density) of each page can vary over time. For queries that scan part or all of the indexes of a table, fragmentation can cause additional page reads, which hinders parallel scanning of data and can significantly affect search performance.

Index fragmentation can result in a decrease in performance and inefficient use of space, and indexes might become quickly fragmented on databases that have only moderate use.

Before you implement an index fragmentation maintenance plan, determine which tables and indexes are most fragmented. Then, create a maintenance plan to rebuild or reorganize those indexes.

For example, in SharePoint 2010, a table that often becomes fragmented is AllDocs, which contains document libraries, their associated documents and lists and list items, and their respective metadata.

The fragmentation level of an index is the percentage of index pages that are not in the same logical and physical order.

Online vs. offline index rebuilds

Online index rebuilding is available in SQL Server Enterprise, Developer, and Evaluation editions only. The methods described in this article take those restrictions into account. The procedures in the article fall back to an offline index rebuild if the edition of SQL Server that is hosting a specific database does not support online index rebuilds, or if the index that is being rebuilt is not eligible for an online index rebuild. An index might not be eligible for an online rebuild due to the presence of large object (LOB) columns, such as columns with a data type of NVARCHAR(MAX), IMAGE, and so on.

For information about online index rebuilds, see How Online Index Operations Work. When an offline index rebuild is performed, table level locks are taken during the rebuild process, which might prevent the table from being written to or even accessed at all. Many of the indexes in SharePoint databases are always rebuilt by using an offline index rebuild because of LOB columns.

Even if online index rebuild is used, there are still two points in the operation where table locks are held momentarily, and these could cause blocking. As a result, we recommend that you always schedule index rebuild activities during periods of low activity.

Measure fragmentation in a SQL Server 2008 or 2005 database (sys.dm_db_index_physical_stats)

In SQL Server 2008 or SQL Server 2005, use the sys.dm_db_index_physical_stats dynamic management view to determine fragmentation for the indexes on a specified table or view.

For measuring fragmentation, we recommend that you monitor the column avg_fragmentation_in_percent. The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent might be acceptable. For more information see sys.dm_db_index_physical_stats.

The following table shows sample results from sys.dm_db_index_physical_stats, with a value of 9.375 for avg_fragmentation_in_percent in one row.

database_id	index_type_desc	alloc_unit_type_ desc	avg_fragmentation_ in_percent
10	CLUSTERED INDEX	IN_ROW_DATA	0
10	NONCLUSTERED INDEX	IN_ROW_DATA	0
10	NONCLUSTERED INDEX	IN_ROW_DATA	0
10	CLUSTERED INDEX	IN_ROW_DATA	0
10	NONCLUSTERED INDEX	IN_ROW_DATA	0
10	CLUSTERED INDEX	IN_ROW_DATA	9.375

To use the sys.dm_db_index_physical_stats dynamic management view

1. On the taskbar, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

   To use sys.dm_db_index_physical_stats with a database object, you must know the database ID and object ID.

2. Select the content database in the Object Explorer, and then click New Query. Execute the following script.

   SELECT DB_ID() AS [Database ID];

   Note

   When you use DB_ID without specifying a database name, the compatibility level of the current database must be 100 (a SQL Server 2008 database) or 90 (a SQL Server 2005 database). If you have upgraded from an earlier version of SQL Server, you must specify a database name in the DB_ID statement. For more information about compatibility levels, see sp_dbcmptlevel (Transact-SQL).

3. Execute sys.dm_db_index_physical_stats on the database or object that you select. You can specify the database, and also a table or index.

   Use the following syntax when you run sys.dm_db_index_physical_stats.

   sys.dm_db_index_physical_stats ( 
       { database_id | NULL | 0 | DEFAULT }
       , { object_id | NULL | 0 | DEFAULT }
       , { index_id | NULL | 0 | -1 | DEFAULT }
       , { partition_number | NULL | 0 | DEFAULT }
       , { mode | NULL | DEFAULT }
   )
   

   Be careful when you use the sys.dm_db_index_physical_stats DMV because it can be very resource-intensive. See Inside sys.dm_db_index_physical_stats for a comprehensive guide that explains the various ways to use it.

Reducing fragmentation for a database

Use the following guidance to reduce the level of index fragmentation.

Run database maintenance Health Analyzer rules

SharePoint 2010 includes the Health Analyzer rules framework. The framework has many rules that monitor the health and well-being of a SharePoint environment and in some instances, takes action to correct certain kinds of issues.

SharePoint 2010 includes several rules that are pertinent to content database maintenance. There are rules that automatically reduce index fragmentation for some SharePoint databases, and rules that check for outdated statistics, updating them if necessary. These Health Analyzer rules replace the updated Database Statistics timer job that was introduced in Service Pack 2 for SharePoint Products and Technologies. By default, these rules are configured to execute on a schedule that varies from daily, weekly, to on-demand, depending on the rule target.

All Health Analyzer rules that are configured to execute daily and that are associated with a particular SharePoint service are executed by the same timer job. Adjusting the scheduling of this timer job will adjust when Health Analyzer rules configured for daily execution and associated to that service will execute during the day. All rules discussed in this article are associated to the SharePoint Timer service.

Health Analyzer rules that are configured to execute on a different time interval (such as weekly) or associated with a different service have distinct timer jobs. If you configure a Health Analyzer rule to execute weekly, that rule executes with the timer job that is configured to execute weekly for the specific service to which that Health Analyzer rule is associated. In other words, the rule executes on the schedule that is defined for that timer job.

You can run Health Analyzer rules manually by clicking Run Now from the ribbon on the Health Analyzer Rules page in Central Administration. Running these rules evaluates the health of indexes and statistics; it also rebuilds and recalculates the index as appropriate.

Databases used by SharePoint have fragmented indices. Running this rule performs the following tasks:

• The rule reports indexes as fragmented. Because evaluating index health is an expensive operation, after the Health Analyzer rule executes, this rule always reports indexes as fragmented to trigger the corrective action.

• For each SharePoint database, the rule action looks for, and if found, executes the proc_DefragmentIndices stored procedure, which builds a listing of all indexes in the database. The present level of fragmentation is evaluated in each index. Any index that is fragmented more than 30 percent is considered for rebuild.

• If the edition of SQL Server supports online index rebuilds, an online index rebuild is attempted for each index. If this fails, (for example, the underlying index might not support online rebuilds because of LOB columns) an offline index rebuild is performed.

As previously noted, this rule does not service every database in a SharePoint environment. Certain databases use different rules to perform similar maintenance activities.

Search – One or more property databases have fragmented indices. This rule maintains the indexes in the SharePoint 2010 Enterprise Search Property Databases. By default, this rule is configured to execute weekly on any server in the farm. All processing for this rule, including corrective actions, occurs during the Check phase of the rule. This means that if you want to manage index rebuilds for the Enterprise Search Property Database, it is not enough to simply configure this rule not to rebuild indexes automatically. You must disable the rule completely if you do not want SharePoint 2010 to automatically run index maintenance operations.

Running Search - One or more property databases have fragmented indices performs the following tasks:

• The rule confirms that the environment is in a state in which it is safe to perform an index rebuild.

• For each property database that is configured for search applications in the local farm, the rule executes the proc_MSS_DefragSearchIndexes stored procedure, which builds a listing of all indexes that have an average fragmentation over 10%.

• Each index in the list that affects the performance of the Property database is rebuilt. If the edition of SQL Server supports online index rebuilds, an online index rebuild is performed. If an online index rebuild is attempted, but fails, the index is rebuilt offline.

Search - One or more crawl databases may have fragmented indices. This rule maintains the indexes in the SharePoint 2010 Enterprise Search Property Databases. By default, this rule is configured to execute weekly on any server in the farm. All processing for this rule, including corrective actions, occurs during the Check phase of the rule. This means that if you want to manage index rebuilds for the Enterprise Search Property Database, it is not enough to simply configure this rule not to rebuild indexes automatically. You must disable the rule complete if you do not want SharePoint 2010 to automatically run index maintenance operations.

Running Search - One or more property databases have fragmented indices performs the following tasks:

• The rule confirms that the environment is in a state in which it is safe to perform an index rebuild.

• For each property database that is configured for search applications in the local farm, the rule executes the proc_MSS_DefragSearchIndexes stored procedure, which builds a listing of all indexes that have an average fragmentation over 10%.

• Each index in the list that affects the performance of the Property database is rebuilt. If the edition of SQL Server supports online index rebuilds, an online index rebuild is performed. If an online index rebuild is attempted, but fails, the index is rebuilt offline.

Search - One or more crawl databases may have fragmented indices. This rule maintains the indexes in the SharePoint 2010 Enterprise Search Crawl Databases. By default, this rule is configured to execute only on demand. When executed, it can execute from any server in the farm.

The rule reports indexes in the crawl database as fragmented because checking for fragmentation in a database is an expensive operation. Simply disabling the ‘Repair’ activity for this rule results in a report that all crawl databases are unhealthy, even when the crawl databases have had their indexes recently rebuilt.

To maintain indexes in crawl databases manually, disable the Search - One or more crawl databases may have fragmented indices rule completely.

Running Search - One or more crawl databases may have fragmented indices performs the following tasks:

• The rule confirms that the environment is in a state in which it is safe to perform an index rebuild.

• For each Crawl database that is configured for search applications in the local farm, the rule executes the proc_MSS_DefragGathererIndexes stored procedure.

• Each index in the Crawl database in the list is rebuilt. If the edition of SQL Server supports online index rebuilds, an online index rebuild is performed. If an online index rebuild is attempted, but fails, the index is rebuilt offline.

Because of the nature of the Crawl database, you typically do not have to defragment this database frequently. Execute this rule after you have first performed a full crawl over your content. Afterwards, monitor the indexes in the Crawl database for fragmentation, and execute this rule when index fragmentation grows. Index fragmentation might occur because of a sudden addition or removal of a large amount of crawled content; for example, during the content expulsion that results from an environmental cleanup, or after the on-boarding of a new content source, such as a file share or large SharePoint web application.

The following databases do not have an automated maintenance mechanism in place. These databases do not typically have much fragmentation. Monitor these databases for fragmentation, and rebuild the indexes in these databases when fragmentation exceeds 30%.

• Search Administration Database

• Secure Store Database

• State Service Database

• Profile Sync Database

• Usage Database

• Managed Metadata Database

• Business Connectivity Services Database

• PerformancePoint Services Database

For more information about the changes that are supported for SharePoint 2010 databases, see Support for changes to the databases that are used by Office server products and by Windows SharePoint Services in the Microsoft Knowledge Base.

If the performance of a heavily fragmented database or table is not measurably improved by frequent defragmentation, you should check the performance of the I/O subsystem.

Reducing fragmentation for a specific table and its indexes

If you want to defragment an index that is associated with a particular table instead of an entire database, you can reorganize or rebuild the index.

• Reorganizing an index specifies that the index leaf level is reorganized. Index reorganization defragments and compacts clustered and non-clustered indexes on tables and views, and can significantly improve index scanning performance. Reorganizing an index uses the existing space allocated to the index. Reorganization is always performed online so that the underlying table is available to users.

• Rebuilding an index specifies that a brand new copy of the index is built. Therefore, a rebuild operation requires enough extra space to build the new copy of the index before removing the old, fragmented index. Rebuilding improves the performance of index scans and seeks. You can rebuild the index with a table online or offline.

The fragmentation level of an index determines the method that you should use to defragment it, and whether it can remain online, or should be taken offline. The following table describes the defragmentation method that is recommended for different fragmentation levels.

Fragmentation level	Defragmentation method
Up to 10%	Reorganize (online)
10-75%	Rebuild (online)
75%	Rebuild (offline)

You can reorganize and rebuild indexes by using the SQL Server 2008 or SQL Server 2005 ALTER INDEX statement, or the SQL Server 2008 or SQL Server 2005 Maintenance Plan Wizard. This article presents the SQL Server 2008 or SQL Server 2005 options only in detail.

Using ALTER INDEX

ALTER INDEX enables a database administrator to perform maintenance operations against an index on a table or view. You can use it to disable, rebuild, and reorganize indexes. Also, you can use it to set options on the index. In most cases, you can rebuild indexes while the database is online, which keeps the data more available to users than an offline index rebuild.

You can customize the following sample script to rebuild all indexes on a table.

USE Contoso_Content_1
GO
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

Fine tuning index performance by setting fill factor

To further improve index data storage and performance, use fill factor. When indexes are created or rebuilt, the fill factor value (1-100) determines the percentage of space that can be filled with data on each leaf level page. The remaining space is reserved for future growth. For many situations, the default server-wide fill factor level of 0 (fill each page to 100% full) is optimal. However, for SharePoint 2010, a server-wide setting of 80 is optimal to support growth and minimize fragmentation.

To view the fill factor value of one or more indexes, query the sys.indexes catalog view. For more information about the view, see sys.indexes (Transact-SQL).

To configure the server-wide fill factor value, use the sp_configure system stored procedure. For more information, see spconfigure (Transact-SQL).

Shrinking data files

In SQL Server 2008 and SQL Server 2005, you can shrink each file in a database (file name extensions .mdf, .ldf, and .ndf) to remove unused pages and recover disk space. SharePoint 2010 databases do not automatically shrink data files, although many activities create unused space in the database. Activities that can create unused space include running the Move-SPSite Windows PowerShell command, and deleting documents, document libraries, lists, list items, and sites.

Figure 3. Database allocation

 

Free space is only released from the end of the file; for example, a content database file of 60 GB with a specified target size of 40 GB frees as much space as possible from the ending (conceptually, the ‘right-hand’ end) 20 GB of the database file. If used pages are included in the ending 20 GB, those pages are later relocated to the beginning 40 GB of the file that is retained. You can shrink database files individually or as a group.

Only perform shrink operations rarely, and only after you perform an operation that removes a lot of data from a database, and then only when you do not expect to use that free space again. Data file shrink operations cause heavy index fragmentation and are extremely resource-intensive. Examples of when you might have to shrink database files are when you relocate a large of number of site collections from one content database to another content database or when you delete a large list. Either of those operations can create large amounts of unused space. Database files can only be reduced to the point where there is no free space remaining. Therefore, a content database in which you rarely delete content might not benefit much from shrinking, and will likely experience a decrease in performance when it has to grow to accommodate additional data without specific accommodations. For more information, see Database File Initialization.

Because shrinking causes index fragmentation, do not shrink database files regularly. Instead, shrink database files only in response to large quantities of unused space that appear as a result of operations that significantly impact the relative amount of used space in a database. If at all possible, avoid shrinking a database.

However, if you must shrink a database, use the following guidelines:

• Do not auto-shrink databases or configure a maintenance plan that programmatically shrinks your databases.

• Shrink a database only when users or administrators remove 50% or more of the content and you do not expect to reuse the unused space.

• Shrink only content databases. Users and administrators do not delete enough data from the configuration database, Central Administration content database, and various service application databases to contain significant free space.

• Shrinking databases is an extremely resource-intensive operation. Therefore, if you absolutely must shrink a database, carefully consider when you schedule the shrink operation.

• After you shrink a database, the indexes in that database are fragmented. Use ALTER INDEX… REORGANIZE to address the fragmentation. If you are not configured to allow instant file initialization, shrink the database to a target size that accommodates the size required for the near-term growth that you expect. For more information, see Database File Initialization.

You can shrink databases and database files manually to recover space by executing the DBCC SHRINKFILE and DBCC SHRINKDATABASE statements in SQL Server 2008 or SQL Server 2005 Management Studio.

For more information about why shrinking a database damages performance and should not be done unless absolutely necessary, see Why you should not shrink your data files.

Shrinking a database by using Transact-SQL commands

DBCC SHRINKDATABASE shrinks the data and log files for a specific database. To shrink individual files, use DBCC SHRINKFILE.

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE uses the following syntax.

DBCC SHRINKDATABASE 
( 'database_name' | database_id | 0 
     [ ,target_percent ] 
     [ , { NOTRUNCATE | TRUNCATEONLY } ] 
)
[ WITH NO_INFOMSGS ]

database_name | database_id | 0 specifies the database name or ID. To select the current database, use 0.

target_percent is the free space, as a percentage, that you want to keep after you shrink the database.

NOTRUNCATE compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file.

TRUNCATEONLY releases all free space at the end of the file to the operating system but does not perform any page movement in the file.

For more information, see DBCC SHRINKDATABASE (Transact-SQL).

DBCC SHRINKFILE

DBCC SHRINKFILE uses the following syntax.

DBCC SHRINKFILE 
(
     { 'file_name' | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

file_name | file_id specifies the file name or ID.

EMPTYFILE migrates all data from the specified file to other files in the same filegroup.

target_size is the target size for the file in megabytes, expressed as an integer.

NOTRUNCATE compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file.

TRUNCATEONLY releases all free space at the end of the file to the operating system but does not perform any page movement inside the file.

For more information, see DBCC SHRINKFILE (Transact-SQL).

Shrinking a database by using SQL Server 2008 Management Studio

Use the following procedure.

To shrink a database by using SQL Server 2008 Management Studio

1. On the taskbar, choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.

2. In Object Explorer, connect to an instance of the SQL Server 2008 Database Engine and then expand that instance.

3. Expand Databases, right-click the database that you want to shrink, choose Tasks, Shrink, Files.

4. Select the file type and file name.

5. Select Reorganize files before releasing unused space. You must also set the Shrink file value. Select this option to release any unused space in the file to the operating system and to relocate rows to unallocated pages.

6. Click OK.

Creating SQL Server 2008 maintenance plans

You can programmatically apply many of the database maintenance operations that are discussed in this article by implementing SQL Server maintenance plans. Maintenance plans can automate and schedule essential tasks to protect your data. By using maintenance plans in SQL Server 2008 or SQL Server 2005, an administrator can schedule operations such as running database consistency checks, reorganizing indexes, or rebuilding indexes. For more information, see the following resources:

• Maintenance Plan Wizard for SQL Server 2008.

• Database Maintenance Plan Wizard for SQL Server 2005.

To configure a SQL Server 2008 database maintenance plan

1. On the taskbar, choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.

2. In Object Explorer, connect to an instance of the SQL Server 2008 Database Engine and then expand that instance.

3. Choose Management, right-click Maintenance Plans, and then choose Maintenance Plan Wizard.

4. Choose Next until you reach the Select Plan Properties page.

   Figure 4. Select Plan Properties page

   

5. In the Name and Description fields, specify a name and description.

6. Decide whether to configure one or more maintenance plans.

   • To configure a single maintenance plan, select Single schedule for the entire plan or no schedule.

   • To configure multiple maintenance plans with specific tasks, select Separate schedules for each task.

   If you have an environment that has 10 or more content databases or more than 200 GB of content, we recommend that you configure separate maintenance plans to provide appropriate specificity and to maximize the maintenance window.

   If you configure multiple maintenance plans for a database, specify a name or description that enables you to differentiate the plans and their purposes, including their schedules.

7. Click Change to set a schedule for one or more plans.

   The Job Schedule Properties dialog box appears.

   Figure 5. Job Schedule Properties dialog box

   

8. Complete the schedule, click OK, and then click Next.

9. On the Select Maintenance Tasks page, select the maintenance tasks to include in the plan, and then click Next.

   Figure 6. Select Maintenance Tasks page

   

   Consider the following notes:

   • A maintenance plan should include index reorganization or index rebuilding; not both.

   • A maintenance plan should never include shrinking a database.

   • To determine the duration of each task, test each task individually before combining tasks into a single plan. You might have to define several maintenance plans on separate schedules to enable tasks to complete during hours that do not adversely affect users.

   • The Maintenance Cleanup Task removes files that are left over after a scheduled maintenance.

10. On the Select Maintenance Task Order page, change the order of the maintenance plan tasks if you need to. Select a task, and then click Move up or Move down. After you order the tasks, click Next.

    Note

    If your databases are very large, you might want to create a separate maintenance plan that checks database integrity less frequently than index maintenance.

    Figure 7. Select Maintenance Task Order page

    

11. Next, the wizard guides you through setting the details for each task. On the Define Database Check Integrity Task page, select the databases to check for integrity, and then click Next.

    Note

    You can safely check all SharePoint 2010 databases for integrity.

    Figure 8. Define Database Check Integrity Task page

    

12. On the Define Reorganize Index Task page, in the Databases list, specify the databases for which you want to reorganize the indexes, select the Compact large objects check box, and then click Next.

    Figure 9. Define Reorganize Index Task page

    

13. On the Define Rebuild Index Task page, if you chose to rebuild indexes instead of reorganize indexes, specify the databases in the Databases list.

14. Select Change free space per page percentage, type 20, and then click Next.

    Change free space per percentage sets the fill factor for the database.

    Figure 10. Define Rebuild Index Task page

    

15. On the Define Maintenance Cleanup Task page, specify the requested values, and then click Next.

    Tip

    We recommend that you delete Maintenance Plan text reports.

    Figure 11. Define Maintenance Cleanup Task page

    

16. On the Select Report Options page, select Write a report to a text file, select a location for the files, and then click Next until you complete the wizard.

    Figure 12. Select Report Options

    

Conclusion

Consistently maintaining the databases that host SharePoint 2010 can significantly improve the health and performance of your system.

Ensure that you have reliable backups for all databases before you implement maintenance operations and maintenance plans.

Before you implement a maintenance plan or specific maintenance operations that run consistently, test the impact of the operations on your system and the time that is required to run them.

As much as possible, set any maintenance operations or maintenance plans to run during off-hours to minimize the performance effect on users.

See Also

Other Resources

This topic also available as a download