10

In our environment the network storage is low on space. At the same time I would like to make sure that we take transaction log backups every 15 minutes instead of current every 6 hours. My question is will changing the log backup interval from 6 hours to every 15 minutes consume more disk space?

2
  • @eckes what does that have to do with backup frequency? If you need space for 3 days of log backups, what difference does it make if the backups are taken every 6 hours or 15 minutes? – Brent Ozar Nov 14 '17 at 22:17
  • Be careful, no matter how often you backup, you should plan enough reserve for your log storage to survive a few days (on weekends?) without a working backup. Unless of,course you can intervene within minutes every time of the day. – eckes Nov 14 '17 at 23:46
13

No, but it may make your transaction log smaller - so your SQL Server will use less space.

To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery model.)

If you back up that log file right away, and you don't have open transactions (or any of a host of other things that will cause SQL Server to keep the log file in use), you'll be able to clear out portions of it, keeping your log file small.

However, if you wait 6 hours, that's 360MB of logged activity - your transaction log file (LDF) would be larger.

I'm using small, easy numbers here - you'd want to change them for your own environment to see the impact on your own transaction log space.

0
6

There's a little bit of overhead in each transaction log file written out to disk, even if no changes occurred in the DB. In some databases I have (SQL Server 2014, compression turned on for backups), transaction logs written during periods of inactivity are generally 9.5 kb per TRN file.

But other than that overhead, the transaction logs are going to contain all changes made since the last TRN file was written, so the amount of total data won't vary significantly.

The more frequently you write the files, the less data loss you risk.

Some experts recommend you run those log backups every minute. Yes, really. I generally try to run them about every 3 minutes during business hours, when the bulk of activity is going on, then about every 10-15 minutes during off-peak times (for systems that only have real activity during work hours, that is, if your system is a 24-hour operation...).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.