You are currently offline, waiting for your internet to reconnect

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

This article describes how to defragment the following Windows SharePoint Services 3.0 databases and Microsoft Office SharePoint Server 2007 databases:
  • Search database
  • Profile database
  • Content database
More information
When the following Microsoft SQL Server script runs, it measures the degree of fragmentation. The degree of fragmentation is defined as the number of blocks that are logically linear but physically nonlinear.

Depending on the extent of the SQL index fragmentation, either an online or an offline defragmentation occurs. An online defragmentation is defined as a defragmentation that does not take SQL locks and that defragments only leaf nodes. An offline defragmentation takes SQL locks and defragments all nodes.

If the percentage of fragmentation is less than 10 percent, no defragmentation occurs. If the degree of fragmentation is from 10 to 90 percent, an online defragmentation occurs. If the percentage of fragmentation is greater than 90 percent, an offline defragmentation occurs.  

The following script tries to perform an online defragmentation first and then switches to offline defragmentation if offline defragmentation is required.

Important The following SQL Server stored procedure makes changes to Windows SharePoint Services 3.0 databases and to SharePoint Server 2007 databases. This script must be run exactly as specified in this article. Changes that you make to SharePoint Server 2007 databases must follow the Support guidelines that are specified in the following Microsoft Knowledge Base article:
841057 Support for changes to the databases that are used by Office server products and by Windows SharePoint Services
Note We recommend that you host the SharePoint Server 2007 databases on a computer that is running SQL Server 2005. We recommend that you monitor the level of fragmentation before and after you run this script. We recommend that you schedule the script to run daily, weekly, or monthly, as appropriate for your situation. 
CREATE PROCEDURE [dbo].[proc_DefragmentIndices]AS    SET NOCOUNT ON    DECLARE @objectid int    DECLARE @indexid int    DECLARE @command varchar(8000)    DECLARE @baseCommand varchar(8000)    DECLARE @schemaname sysname    DECLARE @objectname sysname    DECLARE @indexname sysname    DECLARE @currentDdbId int    SELECT @currentDdbId = DB_ID()    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Starting'    -- Loop over each of the indices    DECLARE indexesToDefrag CURSOR FOR     SELECT         i.object_id,         i.index_id,    FROM         sys.indexes AS i    INNER JOIN         sys.objects AS o    ON        i.object_id = o.object_id    WHERE         i.index_id > 0 AND        o.type = 'U'    OPEN indexesToDefrag    -- Loop through the partitions.    FETCH NEXT    FROM        indexesToDefrag    INTO         @objectid,         @indexid,        @indexname    WHILE @@FETCH_STATUS = 0    BEGIN        -- Lookup the name of the index        SELECT             @schemaname =        FROM             sys.objects AS o        JOIN             sys.schemas AS s        ON            s.schema_id = o.schema_id        WHERE            o.object_id = @objectid        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': ' + @schemaname + '.' + @indexname + ' is now being rebuilt.'        -- Fragmentation is bad enough that it will be more efficient to rebuild the index        SELECT @baseCommand =             ' ALTER INDEX ' +                 @indexname +            ' ON ' +                 @schemaname + '.' + object_name(@objectid) +             ' REBUILD WITH (FILLFACTOR = 80, ONLINE = '        -- Use dynamic sql so this compiles in SQL 2000        SELECT @command =            ' BEGIN TRY ' +                @baseCommand + 'ON) ' +            ' END TRY ' +            ' BEGIN CATCH ' +               -- Indices with image-like columns can't be rebuild online, so go offline               @baseCommand + 'OFF) ' +            ' END CATCH '        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding'        EXEC (@command)        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done'        FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname    END    CLOSE indexesToDefrag    DEALLOCATE indexesToDefrag    RETURN 0GO
Note After you install Windows SharePoint Services 3.0 SP2, you do not have to schedule this script to run for WSS databases (content and config) if you are running SQL 2005. This is because the scheduling and running of the script are included in SP2.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
932744 Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases
For more information about how to perform database maintenance, see the following white paper:

Article ID: 943345 - Last Review: 06/21/2014 13:59:00 - Revision: 7.0

  • Microsoft Windows SharePoint Services 3.0
  • Microsoft Office SharePoint Server 2007
  • kbinfo kbexpertiseinter kbhowto KB943345