Tran Log Backups Very Large

Question

Tran Log Backups Very Large

tknoobOLD

Ten Centuries

Points: 1183
More actions
November 23, 2004 at 10:51 am

#163249

I get transaction log backups over 4 GB even though the transaction log itself is only 2 GB.
How can this happen?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

It shouldn't happen. Are you sure you've got the correct current size? The log could grow itself if you haven't limited it.

tknoobOLD Ten Centuries Points: 1183 More actions · Answer 2

Yes. The tran log size is capped. But it's not growing. Our backups occur every 15 minutes, but every so often we'll get very large backups which are larger than the size of the tran log.

Does a tran log backup backup the active and inactive portion of the log?

Thanks.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

It could be very large depending on whether you backup the log which appends to backup log file or create new one each time you backup the log.

It backups the the transcations which have been committed, you can consider them as inactive portion.

Peter Wright SSCommitted Points: 1966 More actions · Answer 4

If the transaction log backups are appended to the existing backup (i.e. there's no 'WITH INIT' paramater in the backup statement) then the backup file will contain several 15 minute 'snapshots' of the log and will probably grow to several times the size of the log itself.

I can't, however, explain how a single backup of a 2Gb log produces a 4Gb backup.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 5

When does the log file get backed up. If this is happening in a maintenance job and you have shrinkdatabase also turned on, it is possible that the log file grew to 4gig, got backed up, and then the database shrank making it a 2gig file again.

One possibility.

Let us know.

Tom

tknoobOLD Ten Centuries Points: 1183 More actions · Answer 6

The tran log backups are initialize each time.

The shrink database setting is not enabled. The tran log itself is capped.

This normally occurs after our database reindexing job. Very odd.

Jackie Robertson SSC Enthusiast Points: 138 More actions · Answer 7

Is your backup set to append the data or replace existing?

You may need to look at that as well.

magasvs SSCertifiable Points: 7660 More actions · Answer 8

>This normally occurs after our database reindexing job. Very odd.

This is the answer, why log grows. Maintenance plan reindex with DBCC DBREINDEX. And here is an advice from Microsoft:

"To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement." (http://support.microsoft.com/default.aspx?scid=kb;en-us;873235)