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:
- Index Optimization of all AVAILABILITY_GROUP_DATABASES
- 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.