This article will illustrate how a SharePoint database can be fragmented for optimisation which can be applied to either versions of SharePoint: WSS 3.0 or SharePoint 2007.
Before running the script it is important to consider the stability of the system and monitoring the performance and evaluation event logs to ensure the result of running this process is not going to potentially harm your SharePoint environment.
We recommend that you do not alter this script in any way since it could potentially cause an issue to your SharePoint database.
The suggestion from Microsoft is to shut-down your running instance of SharePoint so the database can be completely fragmented, since any locked pages will be ignored in the script.
The recommendation is that you execute this query weekly, monthly, whenever required depending on your environment.
Here is the script to fragment your SQL Server database, to enable this script please complete the following steps:
Step 1: Open SQL Server Management Studio
Step 2: Connect to the SharePoint instance SQL Server
Step 3: Paste the following script into a new SQL Windows
Step 4: Execute the script
Step 5: Create a New Query and type the following command: “exec [dbo].[proc_DefragmentIndices]”
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
For advice how to configure or maintain SharePoint deployment instance please contact Click IT Solutions. We have specialised consultants who have experience with development, configuring and maintaining an instance of SharePoint. We can assist with performance tuning, infrastructure planning and extending the scalability of the SharePoint application. You can contact Click IT Solutions at (02) 9029 5194 or alternatively email us at info@clickitsolutions.com.au.
