WSS 3.0 Database Maintenance

Information:

These are general recommendations for database maintenance of the WSS databases when stored on SQL Server 2005.  While the same concepts apply to SQL Server 2000 some commands may be different.

1)  Nightly Check

a.  Run the following command on a nightly basis on all WSS databases

DBCC CHECKDB 'database_name'

b.  Use the repair option if any errors are found

DBCC CHECKDB 'database_name' REPAIR_REBUILD

Requires the database be in single user mode.

2)  Index Defragmentation

a.  Use sys.dm_db_index_physical_stats to determine Index Fragmentation Level on ‘AllDocs’ table in any Content database.

b.  Example command

Select * From sys.dm_db_index_physical_stats

(DB_ID(N'Content_DB'), Object_ID(N'AllDocs'), NULL, NULL , 'DETAILED');

            Where Content_DB is the name of your content database

c.  If  ‘avg_fragementation_in_percent’ > 10 % then run defragment stored procedure to reduce the level of fragmentation.

  i.  See More Information below for link to procedure.

d.  Only defragment indexes have the DBCC CheckDB commands have been run.

3)  Server-Wide Fill Factor

a.  For WSS a server-wide fill factor of 70 (percent) is optimal

  i.  Run the following commands to configure a server wide fill factor

USE Master

Exec sp_configure 'fill factor', 70

RECONFIGURE

b.   To check the fill-factor value of all user defined indexes run the following command

Select si.name, si.fill_factor

  from sys.indexes si

  join sys.objects so

      on si.object_id = so.object_id

  Where so.type = 'U'

  Order by si.name

4)  Shrink Database

a.  This information applies only if the database size is reduced by 50% because of content deletion

b.  Use the following command to shrink the database

DBCC ShrinkDatabase 'Content_DB',

  free_space_percent, NOTRUNCATE

Where Content_DB is the name of your content database

Where free_space_percent is the percent of free space to                remain after the database has been shrunk.

c.  Only shrink a dabatabase after the DBCC CheckDB commands have been run.

More Information:

How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases

Database maintenance for Windows SharePoint Services 3.0 (white paper)

Miscellaneous SQL Commands

To retrieve the Database ID of the current database

SELECT DB_ID() AS [Database ID]

To retrieve the Object ID of object_name in the current database

Select Object_ID('object_name') as [Object ID]

Configure SQL Server to show advanced options (like fill factor)

USE Master

EXEC sp_configure 'show advanced option', '1'

RECONFIGURE

EXEC sp_configure