Transaction log has huge growth after backup

Question

I am a programmer with a side job as a non-performing DBA.

I have a maintenance plan that performs a full backup and a "database integrity check" every night. I reserve transaction logs every 10 minutes. The size of the transaction log backup increases after backing up the database — an exponentially larger one. I updated indexes and statistics every night - I thought this was what caused the transaction log to spike, but deleting these steps did not change anything.

Mirroring our backups on slow connections would be greatly facilitated if it weren't for this massive surge - so I hope this is what I'm doing wrong. Can anyone suggest something?

Answer 1

If you back up the log only from 6 am to midnight, then the very first log backup at 6 am backs up all database activity that occurred 6 hours after the last log backup.

This is completely normal and probably has nothing to do with the fact that the database is backed up at 4 a.m.

Since you are on SQL2008, the warning in my other answer does not apply, and you should be good at working with log backups for 24 hours.

Answer 2

Is this SQL 2000?

SQL 2000 , " ", , .

. ServerFault "" "", , SQL Microsoft.

. .

SQL 2005 , .

Answer 3

While a full backup is in progress, transaction backups will not be performed. So how long does a full backup last? During this time, the transaction log will not be shortened by the transaction log backups.