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.
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);
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);
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
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.
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.