November 23, 2004 at 10:51 am
I get transaction log backups over 4 GB even though the transaction log itself is only 2 GB.
How can this happen?
Thanks.
November 23, 2004 at 12:02 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 23, 2004 at 12:48 pm
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.
November 23, 2004 at 2:30 pm
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.
November 24, 2004 at 5:33 am
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.
November 24, 2004 at 10:58 am
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
November 24, 2004 at 12:19 pm
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.
November 24, 2004 at 4:06 pm
Is your backup set to append the data or replace existing?
You may need to look at that as well.
November 24, 2004 at 5:49 pm
>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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply