I ran sp_Blitz in our Production server over the weekend and it output a few results regarding the log file growth. Example:
The database [MYDB] has a 104 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.
This DB is part of Always On Availability group and recovery model is Full. Full backups are taken nightly. Log backups are taken every hour.
I am working on shrinking the log files in small increments. In order to do so, I will be first taking the log backup manually. After shrinking, I need to set reduce the log file size to an appropriate size. Auto growth is set to a percentage(bad practice).
My question is: How do I know what is the appropriate size to reduce to? Is there a special formula to keep in consideration when setting the size?
Also, I have noticed that sometimes when I shrink, the log size will not shrink. What is the reason for this and what should I do in this case?
Thanks in advance.
