What this script does?

•Performs backuplog before starting reindexing.

•Performs backuplog after each reindexing, and this prevents the increasing of the log file size.

•Based on the DB fragmentation value, it decides if reorganize or rebuild an index. The Microsoft recommendation is 5% and 30%, and in this example we use 8% and 30%. It is noted that in huge databases, the tables gn_DataTable,s_ObjectTable, s_StoryTable and s_ImageTable are not reindexed anymore.

•The fillfactor is around 70-80, in the future there will be a high fillfactor for the seldomly updated tables, and low for others. Higher the value, the index is smaller, but the update and defragmentation time increase.

•The SQL 2000 compatibility is removed.

Example of results:

A db of 128Gb had a log file of 43Gb: backuplog on reindexing reduced the log size to 11Gb, and rebuilding/reorganizing based on the fragmentation value reduced further 4Gb.

The script code

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

DECLARE @blogindex INT

DECLARE @dbid INT

DECLARE @avdefrag FLOAT;

DECLARE @avdefragminreo FLOAT;

DECLARE @avdefragminreb FLOAT;

DECLARE @fileName VARCHAR(500);

DECLARE @dbname VARCHAR(20);

DECLARE @fileDT VARCHAR(128);

DECLARE @bName VARCHAR(128);

DECLARE @gt DATETIME;

 

SET @dbname = DB_NAME()

SET @dbid=DB_ID(DB_NAME())

 

-- Dati per la deframmentazione

SET @fillfactor = 80

SET @avdefragminreo=8.0

SET @avdefragminreb=30.0

 

SET @gt = GETDATE();

 

 

BEGIN

 --

 -- Reindex

 --

 

 SET @blogindex = 0

 

 BEGIN

          -- 

          -- Backup Log azzero

          --

          SELECT TOP 1 @idLastBackup = [backup_set_id]

          FROM [msdb].[dbo].[backupset]

          WHERE database_name = @dbname

          ORDER BY backup_set_id DESC

 

          SET @fileName = 'BACKUPLOG_I' + CONVERT(VARCHAR(3), @blogindex) + '_LOG.trn';

          SET @bName = @dbname + '-Transaction Database Backup ' + CONVERT(VARCHAR(32), @gt, 120);

 

          BACKUP LOG @dbname TO DISK = @fileName

          WITH COMPRESSION

                         ,NOFORMAT

                          ,INIT

                         ,NAME = @bName

                         ,SKIP

                         ,NOREWIND

                         ,NOUNLOAD;

 END               

 

 SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 

                         table_name + '']'' as tableName FROM INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'' ORDER BY tableName'

 

 -- create table cursor  

 EXEC (@cmd)

 

 OPEN TableCursor

 

 FETCH NEXT

 FROM TableCursor

 INTO @Table

 

 WHILE @@FETCH_STATUS = 0

 BEGIN

 

     SET @blogindex = @blogindex + 1

 

     SELECT @avdefrag=max(avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats (@dbid, OBJECT_ID(@Table), NULL, NULL , NULL);

     

--          PRINT @Table+'->'+CONVERT(VARCHAR(20),@avdefrag)

     

     IF @avdefrag>@avdefragminreo

             BEGIN

       

          IF @avdefrag<@avdefragminreb

                         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REORGANIZE'

          IF @avdefrag>=@avdefragminreb

                         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @fillfactor) + ')'

 

--                                    PRINT @cmd

          EXEC (@cmd)

 

          BEGIN

                 -- 

                  -- Backup Log dopo riindicizzazione

                 --

                 SELECT TOP 1 @idLastBackup = [backup_set_id]

                 FROM [msdb].[dbo].[backupset]

                 WHERE database_name = @dbname

                 ORDER BY backup_set_id DESC

 

                 SET @fileName = 'BACKUPLOG_I' + CONVERT(VARCHAR(3), @blogindex) + '_' + REPLACE(REPLACE(@Table,'[',''),']','') +'_LOG.trn';

                 SET @bName = @dbname + '-Transaction Database Backup ' + CONVERT(VARCHAR(32), @gt, 120);

 

                 BACKUP LOG @dbname TO DISK = @fileName

                 WITH COMPRESSION

                  ,NOFORMAT

                  ,INIT

                  ,NAME = @bName

                  ,SKIP

                  ,NOREWIND

                  ,NOUNLOAD;

          END                       

 END

 

         FETCH NEXT

         FROM TableCursor

 INTO @Table

 END

 

 CLOSE TableCursor

 

 DEALLOCATE TableCursor

END