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.