Resumable Auto-Index Rebuild and Reorganize Script

As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.

To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.

Analyze.sql is executed at first for the database under maintenance. The script will analyse all the indexes in that database and generates a report in tempdb.

Next Defrag.sql is saved as a SQL agent job and executed. This script will defragment the indexes based on the analyses done by the analyze.sql script. If the defrag.sql job fails in the middle, the job is rerun continuously until it completes successfully. The cause for each failure is fixed prior to script rerun.

Scripts are pasted below for your reference.

Analyze.sql
/*Perform a ‘USE ‘ to select the database in which to run the script.*/

— Create the table. 

IF Object_id(‘tempdb..fraglist’) IS NOT NULL

  DROP TABLE tempdb..fraglist;

IF Object_id(‘tempdb..fragstatus’) IS NOT NULL

  DROP TABLE tempdb..fragstatus;

CREATE TABLE tempdb..fraglist

  (

     object_id                    VARCHAR(100),

     index_id                     VARCHAR(50),

     table_name                   VARCHAR(100),

     index_name                   VARCHAR(100),

     avg_fragmentation_in_percent DECIMAL

  )

CREATE TABLE tempdb..fragstatus

  (

     table_name VARCHAR(100),

     index_name VARCHAR(100)

  )

— Declare variables 

SET nocount ON;

DECLARE @tablename VARCHAR(255);

DECLARE @objectid INT;

DECLARE @indexid INT;

DECLARE @indexname VARCHAR(255);

— Declare a cursor. 

DECLARE tables CURSOR FOR

  SELECT table_schema + ‘.’ + table_name

  FROM   information_schema.tables

  WHERE  table_type = ‘BASE TABLE’;

— Open the cursor. 

OPEN tables;

— Loop through all the tables in the database. 

FETCH next FROM tables INTO @tablename;

WHILE @@FETCH_STATUS = 0

  BEGIN;

      DECLARE indexes CURSOR FOR

        SELECT name,

               index_id

        FROM   sys.indexes

        WHERE  object_id = Object_id(@tablename)

      OPEN indexes;

      FETCH next FROM indexes INTO @indexname, @indexid

      WHILE @@FETCH_STATUS = 0

        BEGIN

            — Do the fragmentation check of all indexes of each table 

            INSERT INTO tempdb..fraglist

            SELECT [object_id],

                   index_id,

                   @tablename AS table_name,

                   @indexname AS index_name,

                   avg_fragmentation_in_percent

            FROM   sys.Dm_db_index_physical_stats(Db_id(), Object_id(@tablename)

                   ,

                   @indexid,

                   NULL,

                          ‘LIMITED’)

            FETCH next FROM indexes INTO @indexname, @indexid

        END

      CLOSE indexes;

      DEALLOCATE indexes;

      FETCH next FROM tables INTO @tablename;

  END;

— Close and deallocate the cursor. 

CLOSE tables;

DEALLOCATE tables;

SELECT *

FROM   tempdb..fraglist

Defrag.sql

DECLARE @maxfrag DECIMAL;

DECLARE @minfrag DECIMAL;

DECLARE @frag DECIMAL;

DECLARE @execstr VARCHAR(400);

DECLARE @tablename VARCHAR(255);

DECLARE @indexname VARCHAR(255);

— Decide on the maximum and minimum fragmentation to allow for rebuild or reorg. fragmentation below minfrag will be ignored 

SELECT @maxfrag = 30.0;

SELECT @minfrag = 5.0;

PRINT ‘Started Defrag/rebuild…’;

DECLARE fraglist CURSOR FOR

  SELECT table_name,

         index_name,

         avg_fragmentation_in_percent

  FROM   tempdb..fraglist

  WHERE  index_name IS NOT NULL

         AND index_name NOT IN (SELECT index_name

                                FROM   tempdb..fragstatus)

         AND avg_fragmentation_in_percent > @minfrag

OPEN fraglist

FETCH next FROM fraglist INTO @tablename, @indexname, @frag

WHILE @@FETCH_STATUS = 0

  BEGIN

      IF @frag > @maxfrag

        BEGIN

            SELECT @execstr = ‘ALTER INDEX [‘ + @indexname + ‘] ON [‘

                              + Replace(@tablename, ‘.’, ‘].[‘)

                              +

‘] REBUILD WITH (FILLFACTOR = 80,  SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON)’

    ;

END;

ELSE

  BEGIN

      SELECT @execstr = ‘ALTER INDEX [‘ + @indexname + ‘] ON [‘

                        + Replace(@tablename, ‘.’, ‘].[‘)

                        + ‘] REORGANIZE’;

  END

    BEGIN try

        PRINT @execstr;

        EXEC (@execstr);

    END try

    BEGIN catch

        SELECT @execstr + ‘ : Index operation failed.’ AS descript,

               Error_number()                          AS ErrorNumber,

               Error_severity()                        AS ErrorSeverity,

               Error_state()                           AS ErrorState,

               Error_procedure()                       AS ErrorProcedure,

               Error_line()                            AS ErrorLine,

               Error_message()                         AS ErrorMessage;

    END catch

    INSERT INTO tempdb..fragstatus

    –USE THIS TABLE TO VIEW THE ACTIVITY PROGRESS 

    VALUES      (@tablename,

                 @indexname);

    FETCH next FROM fraglist INTO @tablename, @indexname, @frag

END

CLOSE fraglist

DEALLOCATE fraglist

SELECT *

FROM   tempdb..fragstatus

PRINT ‘Defrag/rebuild completed’;