Select the product you need help with

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

Article ID: 943345 - View products that this article applies to.

INTRODUCTION

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

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/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, 
        i.name
    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 = s.name
        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 0
GO

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.

Back to the top | Give Feedback