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_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.