Microsoft KB Archive/943345

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

Article ID: 943345

Article Last Modified on 11/7/2007

-

APPLIES TO


 * Microsoft Windows SharePoint Services 3.0
 * Microsoft Office SharePoint Server 2007

-



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 measures the level of fragmentation, and then it performs either offline defragmentation or online defragmentation if it is required.

In the following script, the level of fragmentation is defined as the number of blocks that are logically linear and physically nonlinear. If the level of defragmentation is less than 10 percent, defragmentation does not occur. If the level of fragmentation is from 10 percent to 90 percent, online defragmentation occurs. If the level of fragmentation is more than 90 percent, offline defragmentation occurs.

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 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. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragIndexes') BEGIN DROP Procedure  dbo.proc_DefragIndexes END

GO

-- ============================================= -- This stored procedure checks all indexes in the current -- database and performs either offline or online defragmentation -- according to the specified thresholds. -- The stored procedure also updates statistics for indexes in which the last update -- time is older than the specified threshold. -- Parameters: -- @onlineDefragThreshold specifies minimum percentage of fragmentation -- to perform online defragmentation (default 10%). -- @offlineDefragThreshold specifies minimum percentage of fragmentation -- to perform offline defragmentation (default 90%). -- @updateStatsThreshold specifies the number of days since the last statistics update -- which should trigger updating statistics (default 7 days). -- ============================================= CREATE PROCEDURE dbo.proc_DefragIndexes (   @onlineDefragThreshold float = 10.0,    @offlineDefragThreshold float = 90.0,    @updateStatsThreshold int = 7 ) AS BEGIN set nocount on   DECLARE @objectid int DECLARE @indexid int DECLARE @frag float DECLARE @command varchar(8000) DECLARE @schemaname sysname DECLARE @objectname sysname DECLARE @indexname sysname

declare @AllIndexes table (objectid int, indexid int, fragmentation float)

declare @currentDdbId int select @currentDdbId = DB_ID insert into @AllIndexes SELECT object_id, index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL, NULL, 'LIMITED') WHERE index_id > 0

DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes

OPEN indexesToDefrag;

-- Loop through the partitions. FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0 BEGIN

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

SELECT @indexname = name FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid

IF @frag > @onlineDefragThreshold BEGIN IF @frag < @offlineDefragThreshold BEGIN; SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid) + ' REORGANIZE' EXEC (@command) END;

IF @frag >= @offlineDefragThreshold BEGIN; SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + object_name(@objectid) + ' REBUILD' EXEC (@command) END; PRINT 'Executed ' + @command END

IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate) BEGIN SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + object_name(@objectid) + ' ' + @indexname +' WITH RESAMPLE' EXEC (@command)

PRINT 'Executed ' + @command END

FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag

END

CLOSE indexesToDefrag; DEALLOCATE indexesToDefrag; END GO 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

Keywords: kbhowto kbinfo kbexpertiseinter KB943345

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.