Database Maintenance for Microsoft® SharePoint® Products and Technologies

White Paper of Database Maintenance for Microsoft® SharePoint®


This paper describes the recommended maintenance strategies for the databases that host content and configuration settings for SharePoint Products and Technologies.

1. 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)
2. 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)

Database maintenance tasks can be performed by either executing Transact-SQL commands, or running the Database Maintenance Wizard.

Database consistency may be affected when,

· Improper Shutdown of SQL Server.

· Drive Fails

· Noticeable Performance and availability issues.

· Manual edit of tables (UNSUPPORTED)

Check for and repair consistency errors by using DBCC CHECKDB

· Runs DBCC CHECKALLOC

· Runs DBCC CHECKTABLE

· Runs DBCC CHECKCATALOG

· Validates the content of every indexed view in the database.

· Validates the Service Broker Data in the database.

DBCC CHECKDB and performance

Recommended to run DBCC CHECKDB during non-production hours.

To perform table-level consistency checks, you cannot use SQL Server 2005 or SQL Server 2000 maintenance plans, because they perform consistency checks at the database level only. Instead, create a SQL Server Agent job that runs against the individual objects you want to check, using a command such as DBCC CHECKTABLE.

Measure and reduce fragmentation.

Fragmentation occurs when...

· Logical and Physical storage allocation of database contains many scattered area of storage that are insufficient,

· Not physically contiguous,

· Result of many inserts, updates or deletes to a table.

· If tables are fragmented, the indexes will become fragmented.

· AllDocs table becomes fragmented often.

Measure fragmentation in a SQL Server 2005 database.

· Sys.dm_db_index_physical_stats

· DBCC SHOWCONTIG in SQL 2005 (Not recommended because it will be removed in next release of SQL Server)

· Recommended is to use column avg_fragmentation_in_percent.

· Result should be value close to 0 percent for maximum performance.

· However value between 0 to 10 percent may be acceptable.

· While analyzing SHOWCONTIG output important value to look for will be “Scan Density (Best Count: Actual Count)”

Reducing Fragmentation for a Database

· See How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases (http://go.microsoft.com/fwlink/?LinkId=110843&clcid=0x409) in the Microsoft Knowledge Base.

· Recommended is to run weekly.

· Also recommend to run DBCC CHECKDB REPAIR after the above operation.

· The stored procedure provided in above article changes your content database indexes, any changes in procedure is NOT SUPPORTED.

· For heavily fragmented databases you should check object allocation and structural integrity using DBCC CHECKDB.

Reducing fragmentation for a specific table and its indexes

· If you wish to defragment index of particular table and not entire database.

· Reorganizing an index specifies that the index leaf level will be reorganized.

· Reorganization is always performed online, so that the underlying table is available to users

· Its equivalent to the SQL Server 2000 DBCC DBREINDEX

· The fragmentation level of an index determines the method you should use to defragment it, and whether it can remain online, or should be taken offline.

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

· Using the DROP INDEX and CREATE INDEX commands is NOT SUPPORTED.

· SQL 2005 – ALTER INDEX

· SQL 2000 – DBCC INDEXDEFRAG, DBCC DBREINDEX & Maintenance Wizard

Using ALTER INDEX

· Administrator can run against table or view index.

· Can be used to disable, rebuild and reorganize or settings options on index.

· It replaces the DBCC DBREINDEX and DBCC INDEXDEFRAG statements.

· When index is being rebuilt, SHARED LOCK is put on table, only SELECT is allowed.

· SharePoint uses clustered index specifically. When clustered index is rebuilt EXCLUSIVE LOCK is put, preventing any table access.

Fine tuning index performance by setting fill factor

· Fill factor can be used to further improve index data storage and performance

· Default server wide fill factor level is 0 is optimal.

· For MOSS 2007 70 is optimal to support growth & minimize fragmentation.

· Although possible but not recommended to set fill factor for individual tables or indexes.

· Use sys.indexes to view the fill factor value for one or more indexes.

· Use sp_configure stored procedure to configure server wide fill factor value.

Shrinking Data Files

· SP Technologies DO NOT auto shrink data files

· Repeated shrinking may result in increased fragmentation because the operation does not preserve the fragmentation state of indexes

· We do not recommend auto shrink (by any way)

· Shrink only when 50% or more of the content has removed by user or administrator.

· Recommended to shrink only content database. The configuration db, Admin Content, SSP or Search databases, do not usually undergo enough deletion.

· Avoid shrinking by capacity planning for growth by including overhead allocation of 10-20%

· Consider scheduling as shrinking is resource intensive.

· DBCC SHRINKFILE, DBCC SHRINKDATABASE or by using maintenance wizard.

Shrinking a database by using Transact-SQL commands

DBCC SHRINKDATABASE

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

target_percent is the free space in a percentage you wish to retain after the database has been shrunk.

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.

DBCC SHRINKFILE

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. NOTE: Using the EMPTYFILE option is not supported for SharePoint Products and Technologies database files.

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.

Using the TRUNCATEONLY option is NOT SUPPORTED for SharePoint Products and Technologies content databases.

Make sure you keep your backup plans in place before you test out maintenance plans and put them in production.