I need some way to check the space usage and backup the transaction log of the database before it gets full when we run your IndexOptimize.sql script. Our databases are mirrored, so I cannot change their recovery model. Btw, our log files grow hundreds of Gigabytes.

One possibility could be to execute custom commands after each index is processed. This custom command would be designed by the sysadmin to do any custom action, in this case to check the logical space used by the transaction log file and to do a backup log if it is greater of a percentage.

Can you add an argument into your script to do this?
Is there any other way to avoid this problem?


If you are on SQL Server 2016 SP2 or later, you could use the new smart transaction log functionality. That could trigger frequent log backups, when a lot of transaction log is generated (e.g. during index maintenance).

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@LogSizeSinceLastLogBackup = 1024,
@TimeSinceLastLogBackup = 300

There is no option to execute log backups from within the IndexOptimize stored procedure.

  • We don't have SQL 2016. I could schedule a backup log each 10 minutos or something like that anyways, but I am not sure or very confident if SQL will handle this very well (reindex, mirroring, backup log each 10m), that's way I prefer to have different schedule windows for regular backups and database maintenance. "[There is no option to execute log backups from within the IndexOptimize stored procedure]"" Well, that's why I did this feature request. I could customize the script myself, but I am not skilled as you with TSQL. Thank you for your respone anyways.
    – DCA
    Jun 8 '18 at 11:20
  • I should also say that there is no problem backing up the transaction log every 10 minutes all the time. Jun 8 '18 at 19:41

Note that you could have different schedules for the transaction log backups. Normally you might have it run every 15 minutes or 30 minutes, but run it every 5 minutes while the index optimization is running. You can do this manually (set up multiple schedules, timing the increased run based on the usual run times for the index optimization), or dynamically (increase the frequency of the job when you start, reset it when you're done).

Not the answer you're looking for? Browse other questions tagged or ask your own question.