Large transaction log file

Overview
You probably read this because you have a database with a large transaction log file (.ldf). The purpose of this article is to explain:

  • How to manage the ldf file
  • How to make the ldf file smaller
You can say a lot about transaction handling, transaction logging, recovery, various backup types and restore scenarios etc. The purpose of this article is not to dwell on such subjects - that would make this article huge.

How to manage the ldf file
SQL Server logs every modification to the ldf file, for several reasons. You have two options for how to make sure that the ldf file doesn't grow indefinitely:
  1. Set recovery model for the database to simple. Do this if you don't want to take transaction log backups.
  2. Set recovery model to full. Do this if you do want to take transaction log backups.
Yes, it really is that simple, so re-read above points again! So, there are two ways to go wrong:
  • Have simple recovery and attempt to do a log backup. Result: You will get an error message from the BACKUP LOG command.
  • Have full recovery and don't do log backup. Result: ldf file will just grow and grow, endlessly! This is a very common cause for large ldf files.
When you do a log backup, SQL Server will mark space in the ldf file as "re-usable", so that space can be re-used by subsequent log records that are produced. This is often referred to as "truncate the log", or "empty the log". If you have simple recovery, then it is not your responsibility to "empty the log", SQL Server will do this by itself. However, old open transactions or long running transactions will set a limit for how much can be emptied in the log. So, it is perfectly possible to have large ldf files in simple recovery model.

Your recovery requirements
It is imperative that you match your backup and restore strategy to your recovery requirements. This cannot be stressed enough.
  • How much data can you afford to lose? Sometimes referred to as Recovery Point Objective (RPO).
  • For how long can you accept your database to be unavailable? Sometimes referred to as Recovery Time Objective (RTO).
This is a whole topic in itself. Here's a good primer on the topic of RPO and RTO. Don't just set the recovery model to simple in order to keep the ldf file small! Make sure you have a recovery model, backup strategy and restore strategy that supports your RPO and RTO.

How large should the ldf file be?
How long is a piece of string? The technically correct answer to above question is:

Large enough to accommodate the log records produced between your log backups (or whenever SQL Server itself will empty the log, in simple recovery model). Long running transactions taken into account. 
Problem with above is that the answer isn't very helpful. So allow me to generalize a little bit:
  • In simple recovery model, you can frequently get by with a small log file (about 5-10% of the database size). But you still need to accommodate your largest transactions. So if you find that the ldf file keep growing to a certain size, then just leave it at that size!
  • In full recovery, you might just need as large ldf file as the amount of data you have. Say you have a data file of 100 GB, filled to 85 GB. Then don't be surprised if you need an ldf file which is about 85 GB. In the end, it depends on what you do in the database. But if you for instance rebuild all indexes once a week, then you have modified all data in the database and those modifications have been logged to the ldf file. (You can be smarter and only rebuild fragmented indexes, using for instance Ola Hallengren's maintenance procedures.) Rebuilding indexes is only one example; in general, you want to watch out for batches (typically running nights/weekends). It isn't common to during normal "daytime work" produce a massive amount of log records between two log backup occasions. How frequent you backup your log is of course also a factor, common frequencies are once every 10 minutes or once every hour.

How to make the ldf file smaller

First you want to determine whether there are any "blockers" that prohibits SQL Server to re-use space inside the ldf file.
Exeute below command from a query window:
SELECT name, log_reuse_wait_desc FROM sys.databases

Find your database and see what you have in the log_reuse_wait_desc column for the database.

If it is NOTHIN or LOG_BACKUP, then just keep reading.
If it is something else, then you have some blocker that probibits re-use (and in the end also shrinking) of the ldf file. You need to sort this out first. A good Internet search engine is your friend here. If it is REPLICATION, then probably the log reader has stopped processing log records. Sort this up by troubleshooting why it stopped or remove replication if it isn't supposed to be there (some leftover).

Now that you know how to manage the ldf file, you can determine if you think the ldf file is "too large" and you want to shrink it. The ldf file does notshrink by itself, or when you produce a log backup. To shrink an ldf file, you use a command called DBCC SHRINKFILE (documented here). You can do this in SSMS by right-clicking the database, select "Tasks", "Shrink" and "Files". I recommend that you script the command into a query window and execute from there. That allow you to copy the command you executed into forum posts etc. in case you need more help - much better than try to explain how you clicked in the GUI (and also makes it easier to include possible error messages).

If you are in simple recovery model
Just shrink the file! If the file doesn't shrink to the size you want, then try again, and again. If it still doesn't shrink to the size you want, then read this (the "Shrinking of transaction log file" section).

If you are in full recovery model
... and this is where you want to be, then you have to make a decision:

Is it OK to break the transaction log backup chain? 
By this we mean the chain of log backups you may have. If you don't have any prior log backups (perhaps not even a prior database backup), then it is OK to break the chain of log backups. If you do have earlier log backups and want to be able to restore from them up to any point in time then it isn't OK to break the chain of log backups.
  • OK to break the chain of log backups. This is easiest:
    • Set recovery model to simple.
    • Do the shrink according to the steps above (for a database in simple recovery model).
    • Set recovery model to full.
    • Perform a full database backup, so your scheduled log backups have somewhere to begin from.
  • Not OK to break the log backup chain. Then you backup the log (which can produce a huge log backup file, and consequently take some time), and then shrink the file. If the file doesn't shrink to the size you want, then backup log and shrink again, and again. If it still doesn't shrink to the size you want, then read this (the "Shrinking of transaction log file" section).

Commands to do the actual shrink:
  • Simple recovery model
    USE dbname
    CHECKPOINT
    --First param below is fileno for log file, often 2. Check with sys.database_files
    --Second is desired size, in MB.
    DBCC SHRINKFILE(2, 500)
    DBCC SQLPERF(LOGSPACE) --Optional
    DBCC LOGINFO --Optional
     
    Now repeate above commands as many times as needed!

  • Full or bulk_logged recovery model
    USE dbname
    BACKUP LOG dbname TO DISK = 'C:\x\dbname.trn'
    --First param below is fileno for log file, often 2. Check with sys.database_files
    --Second is desired size, in MB.
    DBCC SHRINKFILE(2, 500)
    DBCC SQLPERF(LOGSPACE) --Optional
    DBCC LOGINFO --Optional
     
    Now repeate above commands as many times as needed!
What you end up doing is empty the log (CHECKPOINT or BACKUP LOG) and DBCC SHRINKFILE several times, so that SQL Server can move the head of the log to beginning of the file and also so the end of the file becomes unused. Investigate the layout of the log file using DBCC LOGINFO in between.

If you have loads of VLF (many rows returned from DBCC LOGINFO), you probably had a small file size for the log initially and then had lots of small autogrow. Having lots of VLF is a a performance hit, especially for database recovery and batch modifications.If this is your case, consider shrinking the log file to a very small size and then expand the file size to some comfortable larger size.

Acknowledgements
Thanks for providing feedback and suggestions for this article: Erland Sommarskog. Much appreciated!