Routine SQL database maintenance is essential for the smooth and fast backup SharePoint operation.
Routine SQL database maintenance is essential for the smooth and fast SharePoint backups.
Consistently maintaining the SQL databases that host SharePoint Products and Technologies can significantly improve the performance of SharePoint backups. As much as possible, set any maintenance operations or maintenance plans to run during off hours to minimize the performance impact to users. Before implementing any maintenance operations ensure that a reliable full SharePoint backup exists.
The recommended maintenance tasks for SharePoint databases are not performed.
Switch SQL databases from full to simple recovery mode.
Truncate SQL Log files and shrink SQL data files.
Reduce fragmentation in SQL Server databases: fragmentation occurs when the logical and physical storage allocation of a database contains many scattered areas of storage that are not physically contiguous. The fragmentation level of an index is the percentage of blocks that are logically linear and physically nonlinear. Fragmentation can be the result of many inserts, updates, or deletes to a table. When a table becomes fragmented, the indexes defined on the table also become fragmented. Indexes fragment more rapidly than tables. Rebuild indexes by using the SQL Server 2008 or SQL Server 2005 ALTER INDEX statement, the SQL Server 2008 or SQL Server 2005 Maintenance Wizard, the SQL Server 2000 DBCC INDEXDEFRAG and DBCC DBREINDEX statements
Article ID: 841057: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services (http://go.microsoft.com/fwlink/?LinkId=110812&clcid=0x409)
Article ID: 932744: Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases (http://go.microsoft.com/fwlink/?LinkId=110813&clcid=0x409)
DBCC SHRINKDATABASE (Transact-SQL) (http://go.microsoft.com/fwlink/?LinkId=110852&clcid=0x409)
DBCC SHRINKFILE (Transact-SQL) (http://go.microsoft.com/fwlink/?LinkId=110853&clcid=0x409)
sp_dbcmptlevel (Transact-SQL) (http://go.microsoft.com/fwlink/?LinkId=110840&clcid=0x409
DBCC SHOWCONTIG (http://go.microsoft.com/fwlink/?LinkId=110841&clcid=0x409).
How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases (http://go.microsoft.com/fwlink/?LinkId=110843&clcid=0x409)