If I have a transaction log that is 1.5GB and only 5% used, will the transaction log backup only the 5% of the 1.5Gb (~250MB)? I always assumed so, but I am seeing constant log backups of 250MB (every 10 minutes) even in the middle of the night when there shouldn't be any activity in the database. This observation has led some to believe it is backing up the entire 1.5GB and with compression of the empty space making it smaller.

Does a transaction log backup only what it needs to, or does it do the entire .ldf file?

  • 7
    Why would you not just test that? Why not just create an empty database of 1mb, with a 1.5GB log file, do a full backup, and then do a log backup? Seriously, it'd take less time than asking this question. – Brent Ozar Jan 3 '17 at 21:24

Log backups only backup actual data and do not backup empty space.

It sounds like there are still processes running overnight. Got any index rebuilds or any maintenance tasks (aside from DB backups) that may be running overnight? Just because you think the server should be quiet, doesn't always mean that it is. There could be overnight batch jobs, miscellaneous tasks, all sorts of stuff.

| improve this answer | |
  • There must be additional activity in the db. Thank you for confirming. – John Hennesey Jan 3 '17 at 21:34

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.