Whenever I speak about database maintenance, I always recommend that DBAs backup up their production database transaction logs at least once an hour. As is with most things DBA-related, there is no exact answer that will meet every SQL Server environment’s needs. The interval between transaction log backups depend on many factors, including, but not limited, to how much data the organization is willing to loose, how active the database is, and so on.
When I set up database maintenance plans myself, I generally backup at 15 minute intervals, although sometimes I deviate from this, depending on a variety of different factors.
In my most recent poll, I asked DBAs how often they backup their transaction logs, and the results are shown at the left.
It is interesting to see the various choices made by the various DBAs who completed the poll. Based on my recommendation of backing up transaction logs at least once an hour, (if you add up the results), nearly 77% follow this recommendation, which I think is great.
A few other DBAs choose different transaction log periods. Just over 2% of the DBAs said they never back up their transaction log. I hope this is because their databases use the Simple Recovery Model, and not because they don’t know what they are doing.
A surprising 6% or so said that they don’t even know what a transaction log backup is. Hopefully, after seeing this post, they will do some research and find out why transaction log backups are so important.
Brad M McGehee 
Brad,
My client is trying to do a domain migration on a clustered environment — WinSvr 2008, SQL Server 2005. Our infrastructure MSP has just found out that Microsoft doesn’t support this operation in an as-is state. They’d have to “destroy” the cluster, perform the migration, and create the new cluster. What does this mean for the SQL Server? Will we have to re-install MSS 2005 after the new cluster is created?
Appreciate any insight you might have on this matter,
Happy Thanksgiving!
Michelle
Michelle,
In regard to your question about moving a clustered environment to a new domain, yes, this is not supported by Microsoft. The SQL Server cluster first needs to be removed, then the Windows cluster needs to be removed, the domain changed, and then the Windows cluster added, and then the SQL Server cluster added back. It’s a lot of work, and you before you begin this change, be sure that all aspects of your cluster are well documented so you are able to recreate it when it is rebuilt. Ideally, find a day off when the system can be offline, and give yourself plenty of time to rebuild the cluster, and include extra time to deal with any potential problems that might arise during the rebuild.
Brad, thank you! This is what I was thinking had to happen, and I’m really glad that you could confirm it.
Aloha,
Michelle
Brad,
thank you for the interesting article. We are running an atm system that receives multiple transactions 24*7 , using SQL Server 2005 Std edition 32 bit on Windows 2003 . The veondors recomended that we should set the database Recovery Mode to Simple as they have found that due to the high volume of Transactions SQL Server’s Scheckpoint writer is falling behind and eventually times out as the Transaction Log is growing too big . They claimed that if the Database Recovery Mode is set to Simple, this will not happen . This setting exposes us terribly.Have you or anybody come across such a problem that the database had to be set to Simple Recovery Mode ? Any ideas ?
Lian, yes, if you are running your mission critical system in simple recovery mode, your data is at risk. Whether a database recovery mode is simple or full, each transaction that modifies data is recorded in the log file. The difference is that in simple mode, the transaction log is truncated automatically (and not saved), while in the full recovery mode, the transaction log is truncated when a transaction log is backed up (and saved). Using the full recovery mode, and transaction log backups, should not be a problem if you do the following: 1) Ensure MDF and LDF files are separated on different spindles, 2) Use very high-speed spindles, and as many spindles as practical for high I/O throughput, 3) Store the transaction log backups on their own spindles, 4) Consider using backup compression software, which will reduce the time needed to perform the transaction log backups (while also saving disk space), and 5) Make frequent transaction log backups (you will have to experiment on how often is often enough). There are literally thousands of high performance SQL Server systems that use the full recovery mode, and that is what you should be doing. Of course, before you turn it on for your system, you will want to thoroughly test everything after ensuring all of the above is first in place.
Brad,
thank you for the quick response and advice . You mentioned the MDF and LDF Files should be on their own set of disks. Does that now apply to SANs as well ? Should you create a seperate LUN for the MDFs,DLFS and Tempdb ?
Regards.
SANs are designed to make disk management easy, not for performance. If your MDFs, LDFs, and tempdb are on a SAN, they should be on their own LUN with their own dedicated spindles. Shared spindles, which is common in a SAN, are performance killers.