SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild

Introduction

In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control.

An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the transaction log file can grow until your run out of disk space.
On failure, the transaction needs to rollback. You end up with a large transaction log file, no free space on your transaction log file volume, and an index not rebuilt.

Since SQL Server 2017 with Enterprise Edition, using the Resumable option of index online rebuild operation we can try to keep under control the transaction log file size.

Demo

For the demo, I’ll use the AdventureWorks database with the Adam Machanic’s bigAdventures tables.

Index rebuild Log usage

My transaction log file size is 1 GB and it’s empty.

USE [AdventureWorks2019]
go
select total_log_size_in_bytes/1024/1024 AS TotalLogSizeMB
	, (total_log_size_in_bytes - used_log_space_in_bytes)/1024/1024 AS FreeSpaceMB
    , used_log_space_in_bytes/1024./1024  as UsedLogSpaceMB,
    used_log_space_in_percent
from sys.dm_db_log_space_usage;

I now rebuild the index on bigTransactionHistory.

ALTER INDEX IX_ProductId_TransactionDate ON bigTransactionHistory REBUILD
	WITH (ONLINE=ON);

I had a few autogrowth events bringing my file to 3583 MB. The log space required to rebuild this index is about 3500 MB.

Now, let’s say I want to limit my transaction log file to 2 GB.

Index rebuild script

First, I build a table that contains the list of indexes I have to rebuild during my maintenance window. For the demo purpose it’s a very simple one:

select *
from IndexToMaintain;

The idea is to go through all the indexes to rebuild and start a Rebuild with the option RESUMABLE=ON.
When a rebuild is done the value for the RebuildStatus column is updated to 1.

Here is the code:

WHILE (select Count(*) from IndexToMaintain where RebuildStatus = 0) > 0
BEGIN
	DECLARE @rebuild varchar(1000)
		, @DatabaseName varchar(1000)
		, @TableName varchar(1000)
		, @IndexName varchar(1000)
		, @id int

	select @DatabaseName = DatabaseName
		, @TableName = TableName
		, @IndexName = IndexName
		, @id = id
	from IndexToMaintain 
	where RebuildStatus = 0;

	SET @rebuild = CONCAT('ALTER INDEX ', @IndexName, ' ON ',@DatabaseName, '.dbo.', @TableName, ' REBUILD WITH (ONLINE=ON, RESUMABLE=ON);')
	
	exec(@rebuild)

	UPDATE IndexToMaintain SET RebuildStatus = 1 where id = @id;
END

The commands executed will look like this.

ALTER INDEX IX_ProductId_TransactionDate ON bigTransactionHistory REBUILD
	WITH (ONLINE=ON, RESUMABLE=ON);

The Job is scheduled to be run at a “high” frequency (depending on the file size) during the defined maintenance window. For example, it could be every 5 minutes between 1am and 3am.

We don’t need to use ALTER INDEX with RESUME to resume an index rebuild, we can just execute the original ALTER INDEX command again, as found in the DMV. It’s very useful and simplifies this kind of script.

Alert on Log Space usage

To contain the transaction log file size I create an Agent Alert that will be triggered when the file is used at 50%.In response to this Alert, it will execute another Job with 2 steps.

The first one checks the DMV index_resumable_operations for any running resumable index operation and pauses it.

IF EXISTS (
	select *
	from AdventureWorks2019.sys.index_resumable_operations
	where state_desc = 'RUNNING'
)
BEGIN
	DECLARE @sqlcmd varchar(1000)	
	select @sqlcmd=CONCAT('ALTER INDEX ', iro.name, ' ON ', OBJECT_NAME(o.object_id), ' PAUSE;')
	from AdventureWorks2019.sys.index_resumable_operations AS iro
		join sys.objects AS o
			on iro.object_id = o.object_id
	where iro.state_desc = 'RUNNING';

	EXEC(@sqlcmd)
END

The second step will then perform a Log backup to free up the transaction log space inside the file.

DECLARE @backupFile varchar(1000) 
SET @backupFile = 'C:\Backup\AdventureWorks2019_'+replace(convert(varchar(20),GetDate(),120), ':', '_')+'.trn' 
BACKUP LOG AdventureWorks2019 TO DISK = @backupFile

The command to be executed by this Job:

ALTER INDEX IX_ProductId_TransactionDate ON bigTransactionHistory PAUSE;

Running the Rebuild

I set the RebuildStatus value for my index at 0 and enable the Job (scheduled to run every minute). It starts to run at 13:04.
As we can see in the Job history the index rebuild job ran twice (around 23s) with a failed status. This means that during rebuild it was stopped by the other job doing a PAUSE followed by a log backup.
The third time it runs it could finish rebuilding the index, set the RebuildStatus to 1, and quit successfully.The Job triggered by the alert has been run twice.Two transaction log backups have been performed.While doing the rebuild we managed to keep the transaction log file at a 2GB size compared to the 3.5GB it would use without using the Resumable feature.

Conclusion

This demo was just an example of how the resumable option of index rebuild could be used to contain the transaction file size during index maintenance.
Obviously, this solution is not usable as-is for production. You will find the code on my GitHub if you want to play with it.
I hope you found this blog interesting. Feel free to give me feedback in the comments below.