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:
Support for changes to the databases that are used by Office server products and by Windows SharePoint Services
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 0GO
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:
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: