Index maintenance causing log shipping to go out of synch

Unfortunately log shipping is not very index maintenance friendly, especially if you are using the out of the box maintenance tasks to rebuild indexes. Because they simply rebuild all indexes this can cause enough of a delay that log shipping gets in knots and begins to fail.

The answer is to set up your own custom index rebuild scripts that only rebuild indexes that have a fragmentation level of > a certain percent. e.g 10 percent. Here is the script I normally use. It keeps a record of previous fragmentation levels as well.

------------- Create working table if it doesn't exist  -------------

SET ANSI_NULLS ON
GO

use yourdbname

--drop table _fragmentationReport where tablename like 'Tran%'
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[_FragmentationReport]') --drop table [_FragmentationReport]
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[_FragmentationReport](
	[TableName] [varchar](50) NOT NULL,
	[index_id] [int] NULL,
	[Index] [sysname] NULL,
	[Percent_Fragmentation] [float] NULL,
	[DateCompleted] varchar(30),
) ON [PRIMARY]
END

------------- Gather Fragmentation Statistics on Larger Tables -------------

declare @db varchar(20)
declare @tb varchar(100)
declare @tb_id int
select @db = DB_ID('yourdbname')

DECLARE tbl_cursor CURSOR FOR
   select [name] from sysobjects where type = 'U' and uid = 1
OPEN tbl_cursor

FETCH NEXT FROM tbl_cursor INTO @tb
WHILE @@FETCH_STATUS = 0
BEGIN

	select @tb_id= OBJECT_ID(@tb)
	INSERT INTO _FragmentationReport 
	SELECT @tb,a.index_id, name, avg_fragmentation_in_percent, getdate() --select *
	FROM sys.dm_db_index_physical_stats(@db,@tb_id,null, null,null) AS a
	JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
	WHERE page_count > 50
	FETCH NEXT FROM tbl_cursor 
   	INTO @tb
END

CLOSE tbl_cursor
DEALLOCATE tbl_cursor 
--
/*

select * from _FragmentationReport 
where [index] is not null 
and datecompleted > getdate()-.1 and percent_fragmentation > 5 
order by tablename, percent_fragmentation desc

*/

------------- REINDEX FOR TABLES WHERE FRAGMENTATION > 5 % -------------

DECLARE @tablename varchar(100)
DECLARE @indexname varchar(100)
DECLARE @SQLString varchar(1000)

DECLARE idx_cursor CURSOR FOR
	select TableName,[Index] 	from _FragmentationReport 
where [index] is not null 
and datecompleted > getdate()-1 and percent_fragmentation > 10 
OPEN idx_cursor

FETCH NEXT FROM idx_cursor INTO @tablename,@indexname
WHILE @@FETCH_STATUS = 0
BEGIN
	--SELECT @indexname
	SELECT @SQLString = 'ALTER INDEX '+@indexname+' ON '+ @tablename +
	' REBUILD WITH (ONLINE=OFF,SORT_IN_TEMPDB = OFF)'
	EXEC (@SQLString)
	PRINT 'Rebuilding Index '+@indexname+' on ' + @tablename + ' ' + convert(varchar(24),getdate())
	FETCH NEXT FROM idx_cursor 
   	INTO @tablename,@indexname
END

CLOSE idx_cursor
DEALLOCATE idx_cursor 
Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.