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

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

Solution 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).

Comments

[PJD]

Hi Ian, thanks again for a very in-depth answer which is much appreciated. The reason I wondered about the size was, I was told that the log file had previously been only a few 100MB which at the time didn't sound correct in proportion with the size of the database. Could the size of increased so much from its apparent previous size because its now part of a transactional replication?

[Ian Preston]

Since Transactional Replication does rely on the transaction log, data can't be truncated in the log until replication is completed and it's no longer required. So in that sense it will have an effect, though obviously it's tough to quantify things based on some general information! That said, I would only worry if the log continues to grow out of proportion to any growth in workload; that's what would maybe require some further investigation.

[chue x]

This does not apply to the poster's question. He never mentions anywhere that he is using the simple recovery model. In fact he says he is using the full recovery model.

[David Korb]

I should have written a more descriptive post. It does apply if the database was originally created in simple recovery model and later altered to FULL. This is a nasty bug.

[chue x]

Please edit your post and add the relevant info. This would be helpful, as it would not require everyone to read the entire KB article and figure out what is relevant.

[cdonner]

Wrong - you change to simple and then back to full. technet.microsoft.com/en-us/library/ms189493.aspx