Txn Log backups – does it backup empty space?

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *