If you are using SharePoint 3.0 with Service Pack 2, Microsoft article 943345 says that defrag occurs automatically – so long as you have the full edition of SQL Server 2005.
If you are using SQL Server Express Edition (SSEE), you can use the script given in that article – but you need to adjust it slightly.
SSEE won’t do an online defrag, so the script needs to be changed to always perform an offline defrag. You also need to find out the name of your content database and include that at the start of the script.
Now all you need to do is take your content database offline (you do that from the Applications Management page in your SharePoint website), and run the script.
The SQL script below is a copy of Microsoft’s script with slight modifications to make it work on SSEE.
use WSS_Content_Database;
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 =
@baseCommand + 'OFF) '
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