Jan 18 2011

DBA 101: Why is my log file full?

OverflowingThis question comes up constantly in different venues. I see it sometimes 2-3 times a day on SQL Server Central. I know it pops up at least once a week on Ask SSC. I’m sure I’ve seen it on Twitter #sqlhelp. “Why is my log file growing?” and “Why is my log file full” are heard regularly. Or, the variation, “I ran a full backup but the log file is still full/growing.” occasionally comes up. The people asking these questions are frequently, even appropriately, frantic. I’m writing this blog post for two reasons. First, to try to add a little bit of weight to what must surely be one of the most searched for phrases on the internet when it comes to SQL Server. Second, just to have a shorthand to answer the question, “Here, check my blog post.”

Right off the bat, database backups are not backing up the log. There. That’s out of the way. Yes, I’m serious. Database backups do not backup the log. They backup all committed data in the database (which might include some or all of what might be in your log file) and they backup some transactions that completed during the backup process as part of the cleanup at the end of the backup (that would be, by definition, be in the log file). But they do not backup the log file. Why is this important?

The transaction log on the database represents the work you are doing to that database. It records the row you inserted, the twenty you deleted and the five that were updated. It records the fact that you dropped a table or truncated a table (and yes, truncation is a logged operation). All of these logged events are written to the log. They are written to the log regardless of the recovery model. What recovery model you ask? Ah, and there we begin to hit our problem.

There are three recovery models, full, bulk-logged and simple. The first and third are the ones that most people use, so they’re all I’m going to worry about for this post. Full recovery means that committed transactions (transactions that have been successfully completed) that are written to the log are retained, even after a checkpoint operation. Simple recovery means that committed transactions are removed from the log when the checkpoint operation runs. A checkpoint is basically when everything in memory gets written out to disk (yes, there’s more to it, but that’s enough for our current conversation). Checkpoints occur at irregular intervals.

Assuming your database is in simple recovery, the log only needs to be big enough to hold the transactions that are uncommitted between checkpoints. Depending on the size and number of your transactions, this could mean a small log, or a huge one. Note, I have not said that your log shrinks at checkpoint. It does not. In simple recovery your log is emptied of committed transactions at checkpoint (and yes, I’m repeating myself). This means any space allocated for the log remains allocated for the log. This is one of the reasons that your log file grows and does not shrink. Now let’s talk about the big one.

I first mentioned full recovery and said that the logs are kept, even beyond a checkpoint. This means they stay in the log, waiting. More transactions are run, and assuming your log is growing by default, it gets bigger and more transactions are in the log waiting. This continues until you finally run a log backup process. This is completely independent from your full backups (although a marker for the last full backup is maintained as a part of the recovery process). The basic syntax looks like this:

[sourcecode language="sql"]BACKUP LOG MyDatabaseName
TO SomeLocationOrDevice[/sourcecode]

This is a problem for two basic reasons. By default, new databases are created in full recovery mode AND by default, no one has set up log backups on your system. That means it’s up to you. You have to set up log backups. You have to set them and you have to schedule them and you have to ensure they run if you want to recover your database to a point in time, which is also known as, Full Recovery.

This is the important point, yes, setting the database to simple recovery can largely eliminate the problem of the ever-growing log file. But, simple recovery takes away your ability to recover to a point in time, meaning, crash occurs at 4:57PM. Your last full backup was at 6:00AM. In simple recovery mode, you just lost almost eleven hours worth of business because you can only recovery to the full backup. With Full Recovery, you can do what is known as a tail log backup, and take that in combination with all the other backups and recover your database, at least up to the last log backup, but possibly even right up to when the error occurred.

You need to ask your business, how much data are they prepared to lose. If they can deal with eleven hours, like our example above, great, you don’t need log backups. If, like most businesses I’ve worked with, they expect to recover everything, all the time, you’d better have log backups and full recovery.

19 Comments

  • By WayneS, January 18, 2011 @ 8:07 pm

    Great blog Grant. I’ve bookmarked it so point to others… :)

  • By Grant Fritchey, January 18, 2011 @ 9:00 pm

    Thanks Wayne!

  • By Claire, January 20, 2011 @ 11:51 am

    Thanks for the illuminating post! Not sure why any company, at least after reading this, would go with simple over full–why lose 11 hours of data when you don’t have to?

  • By Jim Sleeman, January 20, 2011 @ 3:25 pm

    Grant, I have to point out that the database backup does in fact backup the transaction log as well. What it doesn’t do is remove all the committed transactions.

  • By Paul Randal, January 21, 2011 @ 10:16 am

    Hey Grant – couple of corrections for you:

    1) a data backup does not just back up committed data. It backs up all allocated pages in the database, whether containing committed data or not.
    2) a data backup backs up all log from the end of the data reading portion of the backup, back to the checkpoint that occurs at the start of the backup, or back to the start of the oldest active transaction at the time the data reading portion ends, or back to the start of the oldest unreplicated transaction at the time the data reading portion of the backup ends. It has nothing to do with whether transactions commit or not while the backup is running.

  • By Grant Fritchey, January 21, 2011 @ 10:35 am

    Thanks Paul. I knew #1, I just phrased it badly. I was off on #2. Thanks for the correction. That’s great stuff.

  • By Kurt Survance, January 22, 2011 @ 8:15 pm

    This covers the principle reasons that a log file will grow continuously, but occasionally you will will be puzzled by a log that is backed up regularly but still grows continuously.

    One common cause is that the database is replicated but the Distribution database has become unavailable. Transaction records involved in replication won’t be removed from the log until they have been sent to the distribution db.

    There are a number of scenarios where this can happen. Most of them that I have seen involve systems where there is not a real DBA taking care of things..

  • By Perry Whittle, January 23, 2011 @ 4:22 am

    Hi Grant
    it always amazes me how many times this subject gets covered and people still don’t digest it, good to cover it as many times as needed though. As you’ve pointed out truncate is a logged operation, i think its important to point out that it’s not fully logged to the extent of a delete operation, only the page de allocations are logged

    Regards
    Perry

  • By Grant Fritchey, January 23, 2011 @ 1:19 pm

    Kurt, it’s true that there are other reasons, but the majority of the time it seems to be that backups are not in place, or that people believe that full backup will truncate the log as well.

    Perry, thanks for the added details. I didn’t want to try to explain everything in a single blog post, but I should have probably included that tid bit. Thanks.

  • By Kurt Survance, January 23, 2011 @ 1:38 pm

    Grant,

    You are right. Not backing up the log is by far the most common reason.

    The people who have this problem are largely people who do not read SQL blogs. I thought I would add something for those who know about backing up their logs but are still experiencing log growth problems. I remember how long it took me to realize replication was the issue the first time I ran into this problem.

  • By heymal, January 23, 2011 @ 10:56 pm

    After read your blog, I suppose that the full backup does not back up the log, so we can not just use the full backup to restore the database to a point in time. We need the log backups to restore the database to the time. But I suspect the full backup contains the log records and log pages. Is it right?

  • By Grant Fritchey, January 24, 2011 @ 8:09 am

    Hello Heymal,

    My post was slightly off. The full backup does backup the log, but you’re correct, it’s not available for point in time recovery. To do that you have to backup both the log and the database. Thanks for stopping by.

  • By BlackHawk, January 24, 2011 @ 8:27 am

    Another good bit of advice that I’ll take for GRANTed. :)

    I think the underlying issue is the way newcomers approach database technology. They haven’t learned that there are mechanisms in place for concurrency and recovery. These ain’t text files we’re dealing with!

    Log files, transactions and commits can cover chapters in a book so it’s understandable for it to be eye-opening for the uninitiated but these topics are the core to building one’s knowledge upon.

    Thanks for adding to the needed literature… and see you over at ASK SSC.

  • By Shaun, January 24, 2011 @ 11:21 am

    Just a quick point..New databases are created based on what recovery option the model database is set to. By default, this is Full, but you can change the model db to Simple and then all your new databases will default to the Simple recovery model.

  • By Grant Fritchey, January 24, 2011 @ 12:43 pm

    thanks for the support BlackHawk.

    Shaun, I meant to mention the model db but I missed it. Thanks for bringing it up.

  • By Dave Schutz, January 24, 2011 @ 3:01 pm

    When I started as the involuntary DBA at my present company all DBs were in simple mode and that was good enough for the bosses. I learned from the SQL community about recovery models and logs and have since changed to using Full and log backups and recovery.
    Thanks to Grant for keeping this subject fresh and to Paul and others for the corrections. The more we share the more we learn.

  • By Monex, February 8, 2011 @ 9:37 pm

    This is complicated by the fact that when you switch a database into the FULL recovery mode it actually behaves as if its in the SIMPLE recovery mode until the log backup chain is established this is commonly called being in pseudo-SIMPLE . ……The trick to the script is finding the last LSN thats been backed up for the database. ……Ive tested this on 2005 SP3 and 2008 SP1…….Note that this doesnt work on SQL 2000 – Ive poked around and cant find a way to get at the LSN without reading the boot page directly which cant be done gracefully inside a function – Ill leave that as an exercise for you.

Other Links to this Post

  1. SQL Server Blogs and news for the week 21/01/11 | John Sansom - SQL Server DBA in the UK — January 21, 2011 @ 6:47 am

  2. DBA 101: Why Don't People Run Backups | Home Of The Scary DBA — February 14, 2011 @ 9:17 am

RSS feed for comments on this post. TrackBack URI

Leave a comment