Our database is set to FULL recovery mode and we take a database backup and a log backup every night. On several occasions we have noticed that the transaction log file is not truncated after the log backup. The log_reuse_wait_desc shows LOG_BACKUP. So we have to run couple of iterations for log backup and sometimes even dbcc shrinkfile command.
Is there any way we can run the dbcc shrinkfile command to shrink the log file every time it reaches 20 GB?
The predefined 'Shrink' maintenance plan is only for the .mdf file which I don't want to do. I believe we'll have to do it through t-sql only and use a trigger but I am unable to make it work. We have a relatively small database and we are running SQL 2012 standard edition. Any pointers you can provide would be immensely helpful.
-
2Why not just set the log file to 20GB, and then disable auto growth if you don't want it growing over that amount? Why do you need to shrink the mdf file?user507– user5072015年11月09日 10:03:53 +00:00Commented Nov 9, 2015 at 10:03
-
1A very bad idea I would say. please note it is not necessary that log backup will definitely truncate the transaction log file. If there is long running transaction which requires a portion of VLF, the log backup would not be able to trunacte it.Shanky– Shanky2015年11月09日 10:24:34 +00:00Commented Nov 9, 2015 at 10:24
-
When you shrink or truncate your Log file, you are breaking your Log recovery chain. This means that any Log backups taken after that point are no good until your next FULL backup. I strongly urge you to find another solution to your problem.RBarryYoung– RBarryYoung2015年11月09日 15:06:15 +00:00Commented Nov 9, 2015 at 15:06
1 Answer 1
Our database is set to FULL recovery mode and we take a database backup and a log backup every night.
I think you need to take log backups much more often than every night. Try every hour to see if that keeps your file growth in check. If not you may need to run them more frequently.
You could also deploy an SQL Agent Alert that triggers when the log percent used reaches a certain threshold. This alert could send an email or fire an agent job that runs a log backup job.
Also I wouldn't ever recommend automatically shrinking files.