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

How Often Do I Backup my Log?

By Steve Jones,

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.

Total article views: 218 | Views in the last 30 days: 8
 
Related Articles
FORUM

transaction log backup

transaction log backup

FORUM

Backup and transaction log

Backup and transaction log

FORUM

Transactional backup in filegroup

Transactional backup in filegroup

FORUM

Transaction Log Backup fails on Monday Morning

Transaction log backup can't find full backup

FORUM

Transaction Log Backup-----Emergency

Transaction Log Backup issue ----Emergency

Tags
backup / restore    
editorial    
w h    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones