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.
- 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.
- Daily isn't nearly frequent enough. See Myth #1
- 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.
