Sql-server – Shrinking T-Log file every weekend basis

index-tuningsql serversql server 2014transaction-log

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.

Best Answer

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.

Related Question