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

Top 6 Myths of Transaction Logs

Top 6 Myths of Transaction Logs
 
I think there is a lot of misunderstanding concerning database transaction logs in SQL Server. I had intended to write this as the top 5 myths, but I wasn't able to narrow the list down that far.
 
Myth #6: I added additional log files to my database for better performance.
 
The truth: SQL Server will only use a single log file at a time. You will not see any performance gain no matter how many log files you create. The only useful reason to add more than 1 log file is if the drive is out of space and you need more log space for a large, long-running transaction. Once the primary log file is full, SQL will move on to the next log file. But again, it is only using 1 log file at any point in time.
 
Myth #5: I don't need log backups for a point-in-time restore. It works when I try it with a full backup.
 
The truth: I've heard this statement many times. The reason for this misconception is that the RESTORE DATABASE command does not return an error or warning if you use the STOPAT argument to tell the restore to stop at a specific time. Furthermore, Books Online is very confusing on the subject as it seems to indicate that it is supported.
 
The command itself is supported, but it will only work if the stopping point is in a log backup. A restore of a full backup will restore to the end of the backup regardless of the value of the STOPAT argument. Buried in all of the descriptions of how to perform the point-in-time restores is the following statement:
The target recovery point must be contained in a transaction log backup.
Myth #4: SQL Server won't allow me to take a log backup after switching to full or bulk-logged recovery model from simple recovery.
 
The truth: This myth is actually true. Part true at least. In order to perform a log backup, the log chain has to be initiated. The only way to initiate the log chain is to perform a full backup. That is part of the reason Books Online says to perform a full backup after switching from Simple Recovery model.
 
So you can take a log backup, but first you have to perform a full backup. And the sooner, the better!!
 
Myth #3: I can't switch to simple recovery mode if my log file is full and I can't take a log backup because I am out of disk space.
 
The truth: This used to be true. In SQL Server 2005 and earlier versions, switching to simple recovery model was a logged operation. So if your log was full, you couldn't switch to simple recovery.
 
In SQL Server 2008, this is no longer true. Switching to simple recovery is not a logged operation and can be performed as an emergency measure.
 
Myth #2: I don't need to perform log backups to manage the log size because I create a full backup daily.
 
The truth: This is wrong in several different ways.
  1. Full backups do not mark the log file as reusable. There are many people that will argue this point, but it is a fact that full backups do not alter the transaction log.
  2. Daily isn't nearly frequent enough. See Myth #1
  3. If you don't need to protect the data in the transaction log, then you should be using the simple recovery model.
Myth #1: My server is too busy to do frequent log backups.
 
The truth: This is undoubtedly the biggest mistake that I see people make with their SQL Servers. The truth is, the busier your server is, the more frequently you should back up the log. One of the biggest performance hits that you can allow to happen to your database log file is an auto-growth event.
 
An auto-growth event blocks all activity in the log file during the expansion. If the log file is designed to expand by a percentage, the defualt is 10%, then the bigger the log file gets, the larger the expansions will be and the longer the event will take to complete.
 
On any server running in bilk-logged or full recovery model, I recommend a minimum frequency of every 30 minutes. On a busier server, I recommend a minimum frequency of every 15 minutes. Many very busy servers back up the logs every 10 or every 5 minutes.
 

Comments

Posted by Brad M. McGehee on 10 March 2010

Great, simple-to-read advice.

Posted by Robert Davis on 10 March 2010

Thanks Brad!!

Posted by Abi Chapagai on 10 March 2010

Robert, good points. I always have confusion in Transaction Log file and the way it works. It gave me more insight.

Posted by Robert Davis on 10 March 2010

Thanks Abi!! Is there anything about tran logs that you'd like to see covered in a future post?

Posted by Robert Cote on 13 March 2010

Every 10 or every 5 minutes, why ?

Posted by SQL_Quest on 13 March 2010

First of all .. a great article .. To add to the Truth about Myth 1.. When you are backing up your T-logs more frequently e.g. every 15 or 10 or 5 mins depending on your environment, it is recommended to turn ON Trace flag 3226 so that the message "log was successfully backed up" will not be written to the SQL Server Logs and to the Windows Application Event Logs.

Posted by TheSQLGuru on 14 March 2010

Clear and concise - I like it!

Posted by Robert Davis on 14 March 2010

@robert.cote -- the end goal is you want to protect your data while at the same time preventing log growth events. Log growth is one of the worst performance hits to your transaction logs. So the more transaction log you generate, the more frequently you should back it up. Paul Randall even tells a story about an online casino that backs up their tran logs every 1 minute.

@kinjal.dba I'm not familiar with that trace flag. Thanks for the info!!!

Thanks SQLGuru!!

Posted by Cris Yarker on 14 March 2010

Well said and concisely put. I'd only add that a frequent (and dangerous) misunderstanding regarding tranasaction logs is their size, or rather their growth.

Truncation and shrinking of log file. The very first thing I do on a new site is check physical available disk space; On three occassions I've found live production disks 99% full !! and had to call the systems/ network manager to create emergency space.

The cause has always been a massively oversized transaction log file (I once found a 25GB database of which 23GB was log file !).

Truncating the log file DOES NOT make it pyhsically smaller. It also has to be SHRUNK to physically release the space back to the operating system.

On backups, my starting point is Full=weekly, Diff=daily, T-log=30mins.

Anything less and you might be looking for a new job.

Also putting the log-file on the same spindle as the data files increases I/O, as the log-file requires only a single serial 'write' wheresas data files require random 'read + write' access.

Posted by Robert Davis on 14 March 2010

Thanks Crispin!! Do you have this info blogged somewhere? If so, please post a link so others can benefit from it!!

Posted by Anonymous on 18 March 2010

This post was mentioned on Twitter by SQLSoldier: Top 6 Myths of Transaction Logs: http://bit.ly/bKIcc8 #sql #sqlserver

Posted by Anonymous on 21 March 2010

Select of SQL Server Best Practices

Posted by Abi Chapagai on 19 April 2010

Robert:

I need a way to track the growth of the log file of all database in a server/instance. I know there are bunch of scripts but if you can please post it in simplified form.Thank you, great work!!!

Posted by Robert Davis on 29 April 2010

Abi, just use insert the output of DBCC SQLPERF(LogSpace) into a table once per day. I'll post a script for that in a few days.

Posted by Abi Chapagai on 22 September 2010

Great, Thank you Robert.

Leave a Comment

Please register or log in to leave a comment.