Shrinking T-Log file every weekend basis

Question

Recently our SQL Server Log drive which has all T-Log files starting growing rapidly.

Considerations

• We expanded drive space to 100 GB.

• It is AOAG environment, SQL Server 2014.

• We have regular backup of T-Log every 15 minutes (but no log drive space is freed up and log drive is increasing more and more each week 50 GB at a lot).

• We cannot afford to expand drive each time.

• Found the reason to be Rebuild-Index task.

• I have deployed Ola Hallengrens code for Index optimization for this.

• Re-build task occupies much space and occupies the drive in close to 15 minutes is 50 GB.

I have been thinking of doing this immediately after the index optimization job completes (Ola Hallengrens code for Index Optimization). I am planning to shrink all the log files on a weekly basis.

Every Sunday @10:00 PM:

1. Index Optimization of all AVAILABILITY_GROUP_DATABASES
2. Shrink all AVAILABILITY_GROUP_DATABASES log files.

Please correct my thinking before I get in a trouble. Because even transaction log backup does not help us anyway as the space is keep on increasing.

What should be the course of action in this case apart from shrinking log file (as it not recommended).

Please any expert advice is highly appreciated.

Thanks for your help.

Answer 1

I would recommend properly sizing the log file and not shrink it on a regular basis.

Because you are rebuilding the index you will grow your logfile as the index will be written to the transactionlog file. This means that your largest index should fit inside the transaction log file.

If your database is in full recovery mode, you can try to take logbackups more frequently to prevent it from growing out of hand.(Multiple index rebuilds stored in the transactionlog file)

If your database is in simple mode, this will not be the case.

When your transaction log file grows you will create something called Virtual Log Files (a structure inside the transaction log file) this can cause several problems. (more information: https://blogs.msdn.microsoft.com/saponsqlserver/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery/)

I would first shrink your file, and then grow it to a proper size to avoid the creation of too many VLF's. But do not shrink them on a weekly basis.

Answer 2

You might find that backing up the TLog in 10 minute intervals will allow for less growth in the TLog file. Re-creating the TLog file to reduce/resize VLFs can help too. Please read Important change to VLF creation algorithm in SQL Server 2014 (Sqlskills.com) on how to accomplish this.

Backing up the TLog every 10 minutes may not help if the index rebuild process is taking an hour. And with an AG you also have to take into account hardening on the secondary so that log can clear on the primary:

When you rebuild indexes there ends up being a significant number of changes that have to flow to the AG secondary. Until all of those changes (transactions) are hardened on the secondary replica, SQL cannot clear the transaction log on the primary (safety feature so that you do not lose transactions).

It doesn't matter which replica you run the log backups on, you will not be able to clear those logs until everything is hardened. This can create significant volume in your TLogs. It's a side effect of running AGs and doing index maintenance.

You need more disk space.

---

_{Community wiki answer Please edit to improve if you can.}