SQL Log File Not Shrinking in SQL Server 2012

Question

I am dealing with someone else's backup Maintenance Plan and have an issue with the log file, I have a database that sits on one drive with a size of 31 GB and a log file that sits on another server with a size of 20 GB, the database is in Full Recovery Model. There is a maintenance plan that runs once a day to do a complete backup and a second plan that does a backup of the log file every 15 minutes. I have checked and the drive that the log file gets backed up to and there is still plenty of room but the log file never gets smaller after the backup, is there something missing from the maintenance plan?

Thanks in advance

Answer 1

The situation as you describe it seems fine.

A transaction log backup does not shrink the log file. However, it does truncate the log, file, which means that space can be reused:

From Books Online (Transaction Log Truncation):

Log truncation automatically frees space in the logical log for reuse by the transaction log.

Also, from Managing the Transaction Log:

Log truncation, which is automatic under the simple recovery model, is essential to keep the log from filling. The truncation process reduces the size of the logical log file by marking as inactive the virtual log files that do not hold any part of the logical log.

This means that each time the transaction log backup occurs in your scenario, it's creating free space in the file which can be used by subsequent transactions.

Leading on from this, should you shrink the file as well? Generally speaking, the answer is no. Assuming your database does not suddenly have massive one-off spikes in usage, the transaction log will have grown to a size to accommodate the typical workload.

This means if you start shrinking the log, SQL Server will just need to grow it again... This is a resource intensive operation, affecting server performance, and no transactions can complete while the log is growing.

The current plan and file sizes all seem reasonable to me.

Answer 2

I don't know if this applies to your situation, but earlier versions of SQL Server 2012 have a bug that crops up when model is set to Simple recovery model. For any database created with model set to Simple, log files will continue to grow in an attempt to reach the 2,097,152 MB limit. This still applies if you alter to Full afterwards. KB article 2830400 states that altering to Full, then altering back to Simple is a workaround -- that was not my experience. Running CU 7 for SP1 was the only trick that worked for me.

The article provides links for the first updates that resolved this bug: "Cumulative Update 4 for SQL Server 2012 SP1", as well as, "Cumulative Update 7 for SQL Server 2012" (if you haven't installed SP1).