Nucleus Datakonsult Large transaction log lile (.ldf)

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


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 no longer your responsibility to "empty the log", instead 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 you find a good primer on the topic of RPO and RTO.
So 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
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 not shrink 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, then 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).
Acknowledgements
Thanks for providing feedback and suggestions for this article: Erland Sommarskog. Much appreciated!