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.