A Wake-Up Call

Last night I received a call from a independent M2M Consultant who needed help with a transaction log that was growing too large. One of the reasons I have this blog is to find others with skills which complement mine. I like to think of us as a group of rebel soldiers with various roles in a Galactic War with a tyrannical emperor, but that’s beside the point. The caller specializes in customizations and is not a database administrator. So, I started to refer him to my blog where I had listed the steps to take to deal with a transaction log growth problem, but then I realized that I hadn’t written it yet. Face palm!

First let me apologize because some personal life issues, as well as a super secret project I’ve been working on for the Made2Manage community, have gotten in the way of my blogging. I promise to do better in the future, starting tomorrow.

Anyway, in this case, the client had set up their full backups at midnight with a single transaction log backup at 11pm. If you’ve read my previous article about SQL Server Recovery Models, you should have an idea of why that’s a bad idea.

Remember that there are two basic components of every SQL database, the data file and the transaction log. The transaction log keeps track of every transaction made since the last full or transaction log backup. I’ll discuss why in the next article, but I generally set transaction log backups every 15 – 60 minutes. Generally speaking, backing up the transaction log clears it’s free space and prevents excessive growth. Since this installation contained 23 hours of processing time in their transaction log every day, it was too large.

Their transaction log was a complete waste of time in this case because it’s purpose is to allow point in time recovery. If you don’t backup the transaction logs and hardware or other failure occurs, then you may lose the entire log. The only protection this kind of backup provided was if the server died between 11pm and midnight. However, during that hour no real work was taking place anyway. I suggested to him that he ask the business owner if he should create a new maintenance plan with more frequent transaction log backups or switch to Simple Recovery Model which doesn’t require them.

Assuming you have good backups, and the transaction log is backed up at fifteen minute intervals, written the to another server (or immediately copied to one after writing them locally) then the greatest loss of data would be less than 15 minutes.

Tomorrow I’ll publish an article covering the creation of simple maintenance plans for SQL Server and I will quickly follow up with detailed instructions as to how to deal with a transaction log growth problem as well.

Stay tuned!

4 comments to A Wake-Up Call

  • Judy G

    Another good blog concerning disaster recovery! Transaction logs have caused all sorts of issues over the years for many customers using M2M. But,knowing how to setup your backup plans correctly, will help eliminate some of the headaches that can be encountered. And knowing how to correctly shrink your transaction log will keep things running smoothly when you do hit a snag.

  • David, a minor clarification point… You say that the transaction log keeps track of every transaction made since the last full or transaction log backup… That’s not quite right. A Full backup does not truncate the log in Full Recovery mode, so the transaction log contains everything since the last transaction log backup, even if a Full backup occurred in the meantime.

  • Mark, yes you’re right. I tend to simplify things because the typical person who reads my blog isn’t a DBA. However, in this case I clearly mis-spoke. My brain was thinking about database restores where SQL parses the log to determine the next record after the last full backup.

    Anyway, thanks for the clarification.

  • By the way, David, I meant to include in my comment that I was sure that was just a slip of the keyboard. I have been reading your blog for a few months now and even though I don’t use the product, I find good stuff in your writing and enjoy it.

    And the core point of your post is great. I have a blog entry forming in the back of my mind on the topic of frequency of log backups, too, so your post jumped off the page at me, so to speak.

    I hope we get a chance to meet in person at the PASS Summit this year.

Leave a Reply to Mark (AjarnMark) Caldwell




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>