Do you know how often to back up your transaction log in SQL Server? Most of us have stock answers, hopefully answers that have time intervals like every hour. I worry that most people have a time interval of “never” because their answer is “I make full backups and don’t need to backup logs” or “What’s a transaction log?” That’s a different discussion and if those are your answers, I have an article for you to read.
Managing a transaction log is a bit tricky and not straightforward. I think far too many people manage their logs based on the space they are trying to maintain on disk. However that’s not the way you decide when to back up logs. The way you decide how to back up a log has nothing to do with space. It’s best stated in this quote from Gail Shaw: “the log backup interval should be less than the allowable data loss for the system.”
Simply put, decide how much data you can afford to lose. Or how much loss will exceed your tolerance for being yelled at. That’s your log backup interval. Make a log backup after that much time passes. Schedule that interval into your maintenance operations and observe how big the log backups are. That will determine your log size, not the other way around.
As with most guidance and advice, this is based on a time and place in the platform’s lifecycle. This is the advice I’ve used from SQL Server 7 to 2012, but it’s subject to change, so make sure it’s still valid if you’re reading this in 2020.
And, of course, make sure that you also understand how to restore these backups in case you do have an issue. That’s probably the most important thing to know.
Steve Jones
The Voice of the DBA PodcastListen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and Mevio . The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. |
Voice of the DBA 
I’ve heard Brent Ozar recommend doing the backups every minute as a way of spreading out the load. His logic was why would you let things trickle in for an hour then pound your network and disks doing a large backup when you can let them trickle out. Although I somewhat agree, and you never actually deal with the files personally, 1,440 backups per database per day seems like a bit much. These are separate files, separate entries in the error log, separate entries in MSDB, and doing 1/5 of that by having backups every 5 minutes sounds like a better base value to me.
Gail Shaw is right, she usually is, that you would use less than the acceptable data loss. If you think about it, to use these backups and not be able to roll logs down to the second, you would be talking about a total crash. Be that very rare corruption, multiple disks failing in the SAN at the same time, etc, and that type of failure usually leaves you looking good if you say “we only lost 4 minutes of data”. However, that’s not my call, it’s yours.
The only thing I would advocate against is don’t do the log backups every hour or every four hours as I’ve seen several times before. The more often you do it the less expensive the process is, with you only paying for it by having more files and log entries.
LikeLike
I’m not quite sure I understand. Every hour isn’t good enough? Because of data loss? That might be fine for some places, and the administrative overhead is lower. I know that automating the restore of more files isn’t hard, but it’s also potentially more of a constant load on your I/O system and it means that if you struggle to move lots of files or a process copying them fails, it’s easy to have something like that go on too long, even hours, and then trying to explain why it wasn’t a top priority.
I think it’s a balance and you need to look at RPO/RTO here. Doing files every minute or 5 minutes isn’t necessarily a problem, but it can be.
LikeLike
Every hour has two big disadvantages.
First, if your server goes down hard, especially if your logs and data were on the same RAID array (only your SAN admin really knows), then you can lose more data. You’d have to be paranoid to worry about this, but I am a DBA.
Second, I’ve never seen it take longer to copy (12) 100 MB files than it does to copy (1) 1,200 MB file or take noticeably longer to restore from them. However, I have seen it take longer to create that 1,200 MB file from doing hourly logs as opposed to doing a 100 MB backup every 5 minutes. This holds even more truth when all of your servers are trying to back up to the same server on the hour, every hour. I’m more comfortable with this load being spread over the hour.
As for the copy of these files failing, if they fail then you start that file over. It can happen with any file transfer, and I’m not sure what the argument is here. At least with separate smaller files you can start where things went wrong instead of starting from scratch.
For the record, I do think Brent’s suggestion of every minute is a little overboard, and not something I’ve ever done.
LikeLike
You’re conflating a few things here. First, the timing of backups has nothing to do with storage of log/data/backups. That’s separate and should be. If they’re not on physical devices, they should be. You should ask the question, and explain to SAN admins, and management, the risks. I’d argue that putting backups on the same SAN is a risk. Better to drop to some server with local storage or a second SAN.
In terms of copying, I agree that larger files can be slower, but don’t put your environment onto that of many others. Lots of people have small backups for logs. Some have large ones. I’ve had both, and I wouldn’t say that backing up every hour or every minute is a rule of thumb. It’s a decision driven by your requirements and environment.
You’re also conflating the load on servers. If every server goes every minute, I could have an issue with a central server. not to mention that if I have issues with the server I store backups on, and I will at some point.,I will have lots of jobs constantly failing every minute, or 5 minutes. If I go once an hour, I have some time to respond before things look like they’re falling apart. If I go once an hour, I can also stagger my load on the central server (and network), sending files from a different server every 5 minutes, but once an hour. I’ve used this before, moving log backups and full backups to separate schedules to stagger the impact across all systems.
The other thing I like is that with 24 files in a day, I can easily tell if one is missing. If I miss one of 24, it’s easier for a human to detect issues, and restart copies than if I am missing 1/1000 files. Note that good scripting and automation in your copy process, including restart, or recopy of files not complete, is important.
At the end of the day, it depends. There are a lot of factors and I wouldn’t recommend against once an hour or once every five minutes on general principle. The situation needs to be examined.
LikeLike