question

No personally dont have a 2000 script (no 2000 DB in my "estate"). Just found [this][1] blog while searching the t'internet.. I have had a quick scan through it and it looks to do the trick. Personally I would test 1st and then change the percentages base on what is good for your system/DB. **NOTE:** As I have stated, I dont have a 2000 DB to test the script. [1]: http://sqlserverblogforum.blogspot.com/2011/03/index-defragmentation-script-for-sql.html

Half a tick - I'll dig out the script I wrote to do this on SQL2k... edit: Right. Here goes: Create the SP below somewhere sensible. As you see, it takes two parameters - the first is for a specific database name (if not supplied, it'll do them all) - and the second is an upper limit of time to run in minutes - if not supplied, it'll run for an hour. It owes a lot to Ola Hallengren's script ( ) and to Michelle Ufford's script ( ), but it's a lot simpler, and doesn't have decent (or any) error handling. You have been warned. This is worth what you paid for it, no warranty. Having said that, I've been running it daily in a production environment for 6 months without issue... CREATE Procedure SmartIndex ( @DatabaseName varchar(255) = NULL, @RunDurationMinutes INT = 60 ) WITH ENCRYPTION AS SET NOCOUNT ON -- Sanity check If (@RunDurationMinutes < 0 ) OR (@RunDurationMinutes > 300) BEGIN SELECT @RunDurationMinutes = 60 END IF @DatabaseName = '' BEGIN SET @DatabaseName = NULL END Declare @EndDate datetime SELECT @EndDate = dateadd(minute, @RunDurationMinutes, getdate()) CREATE TABLE #ConsFragList ( DatabaseName CHAR (255), DatabaseID INT, ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL, IndexDepth INT, Owner char(255)) CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) IF @DatabaseName IS NULL BEGIN exec sp_msforeachdb 'use [?] INSERT INTO #fraglist exec (''DBCC showcontig WITH TABLERESULTS, FAST'') INSERT INTO #ConsFragList SELECT db_name(), db_id(), f.*, INDEXPROPERTY (ObjectId, IndexName, ''IndexDepth''), u.Name AS Owner FROM #FragList f LEFT JOIN sysobjects o on f.ObjectID = o.ID LEFT JOIN sysusers u ON o.uid = u.uid WHERE o.xtype <> ''s'' TRUNCATE TABLE #FragList ' END ELSE BEGIN exec ('use [' + @DatabaseName + '] INSERT INTO #fraglist exec (''DBCC showcontig WITH TABLERESULTS, FAST'') INSERT INTO #ConsFragList SELECT db_name(), db_id(), f.*, INDEXPROPERTY (ObjectId, IndexName, ''IndexDepth''), u.Name AS Owner FROM #FragList f LEFT JOIN sysobjects o on f.ObjectID = o.ID LEFT JOIN sysusers u ON o.uid = u.uid WHERE o.xtype <> ''s'' TRUNCATE TABLE #FragList') END --select * from #ConsFragList --WHERE LogicalFrag > 10 -- TO DO --AND Extents > 1 AND IndexID > 0 AND IndexDepth > 0 AND CountRows > 1000 --ORDER BY LogicalFrag DESC DECLARE @DBName varchar(255) DECLARE @ObjName varchar(255) DECLARE @IndName varchar(255) Declare @Owner varchar(255) DECLARE @LogicalFrag int declare @sqlcmd varchar(1024) declare DefragThese CURSOR FOR SELECT RTRIM(DatabaseName), RTRIM(ObjectName), RTRIM(IndexName), RTRIM(Owner), LogicalFrag FROM #ConsFragList WHERE LogicalFrag > 10 --AND Extents >1 --AND IndexID > 0 AND IndexDepth > 0 AND CountPages > 100 ORDER BY LogicalFrag DESC OPEN DefragThese FETCH NEXT FROM DefragThese INTO @DBName, @ObjName, @IndName, @Owner, @LogicalFrag WHILE @@FETCH_STATUS = 0 BEGIN SELECT @DBName = '[' + @DBName + ']' SELECT @ObjName = '[' + @ObjName + ']' SELECT @IndName = '[' + @IndName + ']' SELECT @Owner = '[' + @Owner + ']' IF @LogicalFrag > 30 --TODO BEGIN SELECT @SQLCmd = 'DBCC DBREINDEX (''' + @DBName + '.' + @Owner + '.' + @ObjName + ''', ' + @IndName + ') WITH NO_INFOMSGS' END ELSE IF @LogicalFrag > 10 -- TODO BEGIN SELECT @SQLCmd = 'DBCC INDEXDEFRAG (' + @DBName + ', ''' + @Owner + '.' + @ObjName + ''', ' + @IndName + ') WITH NO_INFOMSGS' SELECT @SQLCmd = @SQLCmd + ' USE ' + @DBName + ' UPDATE STATISTICS ' + @ObjName + ' ' + @IndName END -- print @SQLCmd exec (@SQLCmd) WAITFOR DELAY '00:00:05' IF getdate() > @EndDate BEGIN WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM DefragThese INTO @DBName, @ObjName, @IndName, @Owner, @LogicalFrag END END ELSE BEGIN FETCH NEXT FROM DefragThese INTO @DBName, @ObjName, @IndName, @Owner, @LogicalFrag END END CLOSE DefragThese DEALLOCATE DefragThese drop table #ConsFragList drop table #FragList GO