Article ID: 943345 - Last Review: March 30, 2009 - Revision: 3.0

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

Expand all | Collapse all

INTRODUCTION

This article describes how to defragment the following Microsoft Windows SharePoint Services 3.0 databases and Microsoft Office SharePoint Server 2007 databases:
  • Search database
  • Profile database
  • Content database

MORE INFORMATION

The extent of the Microsoft SQL Server index fragmentation determines whether a fragmented database will be defragmented by an online defragmentation process or by an offline defragmentation process. In online defragmentation, only the SQL Server leaf pages are defragmented, not the SQL Server locked pages. In offline defragmentation, the locked pages and all the leaf pages are defragmented.

The following SQL Server script tries to perform an online defragmentation first then it switches to offline defragmentation where required.

Important The following SQL Server stored procedure makes changes to Windows SharePoint Services 3.0 databases and 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. 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 These steps will not be needed for WSS databases (content and config) after Windows SharePoint Services 3.0 SP2 if you are running Sql 2005.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
932744  (http://support.microsoft.com/kb/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 whitepaper:
Database maintenance for Office SharePoint Server 2007
http://technet.microsoft.com/en-us/library/cc262731.aspx (http://technet.microsoft.com/en-us/library/cc262731.aspx)

APPLIES TO
  • Microsoft Windows SharePoint Services 3.0
  • Microsoft Office SharePoint Server 2007
Keywords: 
kbinfo kbexpertiseinter kbhowto KB943345
 

Article Translations