Routine database maintenance is essential for the smooth operation of Microsoft® SharePoint® 2010 databases. This white paper describes the database maintenance tasks supported for SharePoint 2010.
The recommended maintenance tasks for SharePoint 2010 databases include:
• Checking database integrity.
• Defragmenting indexes by either reorganizing them or rebuilding them.
• Setting the fill factor for a server.
Note: This article discusses database maintenance and not planning for capacity or performance. For information about capacity or capacity planning, see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010) (http://go.microsoft.com/fwlink/?LinkId=217482).
Although previous versions of SharePoint Products and Technologies required manual intervention to perform index defragmentation and statistics maintenance, SharePoint 2010 automates this process for its databases. This is accomplished by several SharePoint Health Analyzer rules. These rules evaluate the health of database indexes and statistics daily, and will automatically address these items for these 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
Database maintenance tasks can be also performed by either executing Transact-SQL commands, or running the Database Maintenance Wizard. This whitepaper will initially present the Transact-SQL commands that you can use, and then explain how to create database maintenance plans by using the Microsoft SQL Server Database Maintenance Wizard.
Note: For the T-SQL approach I generally prefer Michelle Ufford’s SQLFool Defrag Script.