Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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:

BACKUP LOG MyDatabaseName
TO SomeLocationOrDevice

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.

Comments

Posted by Steve Jones on 18 January 2011

Why do I feel this won't be the last article or blog on this topic?

Posted by Grant Fritchey on 18 January 2011

Hey, I couldn't believe I was actually writing one. There must be fifty of them out there, but they seem to be invisible.

Posted by Barry G Freeman on 27 January 2011

It's because every DBA should know this as a matter of course, yet I've seen several that DON'T know that a full database backup does NOT backup the log, only a LOG backup frees up space in the transaction log.

One thing you might mention: tempDB doesn't as a rule, get backed up, either full or log. Therefore the Transaction Log file can grow enormous and yet have 99% free space. It's a good first point of call if your log drive is running short of space.

Posted by Grant Fritchey on 27 January 2011

Good point on the tempdb. That might be worth a post on it's own. BTW, I'm slightly off when I say the log doesn't get backed up with a full backup. It does backup the log, but it does nothing towards getting the log truncated. That only happens with the log backup, as I said.

Posted by Nakul Vachhrajani on 27 January 2011

I like the part where you have emphasized that a database backup is different from a log backup, and that it is the user who is responsible for setting these up. Great, concise article; and as Steve said, this will not be the last one out there!

Thanks again!

Posted by Jamie Batiste on 27 January 2011

Succinct, to the point and well said. Means I can point people at it now and explain only the detail needed, not the whole process!

Posted by Abi Chapagai on 27 January 2011

Good article, Grant!. Managing transactiong logs and tempdb is a critical task for a DBA.  

Posted by hor_netuk on 27 January 2011

Excellent article!

Takes me back to when I first was tasked with looking after a SQL DB (from an Oracle background).  I switched to Simple mode (log file was beyond huge), hit BOL and read up on SQL Server backup models. This is truly the first SQL Server lesson I learned.  Have worked with SQL Server ever since and keep my log files backed up!

Posted by tferguson on 27 January 2011

Good article, thanks.

Might mention that truncation of the log (full or simple recovery mode) only removes data prior to the oldest active transaction.  An old open transaction with locks can cause the log to eventually fill, even in tempdb.  DBCC opentran(dbname) finds the culprit.

Posted by Aaron Gonzalez on 27 January 2011

"By default, new databases are created in full recovery mode"

I would add that this can be changed by changing the recovery model of the model database.

Does that make sense?

Posted by Grant Fritchey on 27 January 2011

Yes, that makes perfect sense and it's something I should have included. A small detail, but an important one. Thanks for pointing it out.

Posted by Tom Garth on 28 January 2011

Grant,

Simple and easily understood.

I would like someone to describe how they would order the typical items in a full blown single Maintenance Plan and why. The wizard walks you through them 1 at a time, but does everyone agree with the order.

I've never been sure.

Posted by lifoet on 19 February 2011

good succint info!

Thanks

Posted by david.shink on 10 August 2011

I just came across this article.  When I first ran into the big trn log problem, I tried switching my db to simple recovery, & backing up the db with copy_only.  I had been backing up the trn logs all along.  The backup truncated the trn log, but I was surprised when the trn logs were the same (big) size as before.  Dummy here finally realized that the backup truncates the trn log, but does not shrink it.  I added the step of shrinking the trn log after the backup. (I shrink trn logs, but never mdf or ndf files).  I then change recovery from Simple back to Full.  My trn log is now small, & everything works fine.

         One of the things I liked best about this post was that it made me think that I could post too.  Thanks for the encouragement.

Leave a Comment

Please register or log in to leave a comment.