The correct answer is: it depends. That may seem like a cop out, but there are myriad considerations when deciding how often the transaction log for each SQL Server database should be backed up.
The Value of a Transaction Log and Transaction Log Backups
What is a SQL Server transaction log, and how does it help maintain secure databases? A transaction log is a record of the modifications made to a database. It would include any new or updated data or tables, data deletions and more. Think of it as a log detailing everything a user does to change the database.
A transaction log backup is exactly what it sounds like — a backup of that log file that’s stored and kept for use in the case of database corruption, a power outage or any other event that causes loss of data or work on the database. It’s used in combination with full database backups and differential backups to restore a database when an event like this occurs (and, trust us, it will and does — learn more about database backups).
How Backup Frequency Affects Data Recovery
The more frequently you back up your transaction log, the less data you stand to lose. That’s why, if you ask a business leader or other stakeholder how often they want the log backed up, they may say, “Every minute. I don’t want to lose anything.” The other benefit to more frequent backups is that you keep the transaction log that resides on your system smaller as data is offloaded. If you back up once a day, for example, that transaction log gets large, as it’s keeping track of all changes to the data throughout the day. If you do it every 15 minutes, you’re restarting your log in 15-minute increments, and the file stays smaller.
You might be thinking, “Every minute it is, then.” Not necessarily, because there are disadvantages to backing up too frequently. First, there’s an administrative cost to managing all of those backup files that reside in storage. Fifteen-minute increments multiplied by 24 hours means there are 360 files created every day that need to be stored and managed.
The other “cost” is in the efficiency of your backup database. The more files you have, the longer it takes to search through them and retrieve the appropriate file when you need it.
Top Considerations When Setting Backup Frequency
So, what’s a DBA to do when setting transaction log backup frequency? The first and most important thing to do is consult business leaders to find out how the data is used. Work to understand how often the data is changing. If it’s a real-time sales database with thousands of records and hundreds of salespeople entering and modifying orders, it’s changing quite a bit, likely by the minute. If it’s a reporting database that aggregates sales data and is accessed weekly or monthly, not so much.
Next, ask how easy it is to repeat transactions, because that’s what you’ll need to do with the log backup. In a sales setting, repeating 15 minutes’ worth of transactions would likely prove difficult, especially if users are taking orders over the phone. If it’s a document imaging system, it may be a simple matter to run all of that period’s documents back through the scanner before they are shipped to the warehouse or shredded.
The DBA’s job is to help business leaders understand the process for recovering data and consider what would happen if a database became corrupted or went down for some reason, along with considerations for storage and efficiency with frequent backups.
Most DBAs will say that every 15 minutes is the standard frequency for transaction log backups. However, some databases might need to be backed up more frequently and others less frequently. You want to balance the risk of losing data with the storage and management cost of the files to decide on a frequency that’s best for the business.
Backup Maintenance
How long do you need to keep those backups? Your MSDB system database maintains historical backup information indefinitely, unless you schedule jobs to purge on a regular basis. As you can imagine, that database can become very large over time, which uses up system resources and reduces performance when you need to retrieve a log. Setting up jobs to purge old backup history information is essential to responsible database management.
But how often should you purge? Again, you’ll need to consult business leaders. Some businesses may be contractually or legally required to keep a certain amount of backup logs to prove their ability to restore databases. In other cases, these requirements may not be present. Work with your stakeholders to determine a good schedule for purging that ensures you’ll be able to access the backups you need without storing too many of them.
Contact SQL Tailor Consulting Today
As you consider how often to back up transaction logs and other SQL Server questions, remember that SQL Tailor Consulting is backed by 25 years of database management experience and can help you make the right decisions for your company. Give us a call at (248) 919-8086 for a free consultation.
