Unusually large backup size of transaction log?
-
Hi,
We have a SQL 2000 database set with the Full recovery model. A full database backup is performed each night at 1am. The transaction log is backed up every hour (on the half hour) eg. 12:30, 1:30, 2:30 etc.
The size of the .mdf is 4Gb, with an .ldf of 88Mb. There is around 1Gb free space in the database. The backup of the transaction log at 12:30 (just before the database backup) is ~100Kb. This size increases to 4Gb at 1:30 (after the database backup), then reduces back to ~200Kb at 2:30. The database is also re-indexed and shrunk at 2:40, causing the next transaction log backup at 3:30 to be 3Gb in size. What could the cause for the increased transaction log size be? There is no activity on the database at that time of night.
The size increase after the database backup is confusing. But could the increase after the re-indexing be due to the Full recovery model? Would changing this to Bulk-logged before the re-indexing help at all?
Thanks for any help offered.
Steve.
-
The t-log size would definitely increase after the reindex operation and you have rightly mentioned that you can change the recovery model to bulk logged before the reindex operation and change it back to full recovery after the job finishes. Regarding the increase in size of t-log after the full backup that is really strange and needs to be further investigated
-
The reindexing isn't a bulk operation.
I would try to make extra transactionlog backups during the indexing to keep the size small
-
Jo Pattyn (9/26/2007)
The reindexing isn't a bulk operation.I would try to make extra transactionlog backups during the indexing to keep the size small
I agree with Jo,
indeed, switching to bulk recovery mode may increase the transaction log size for DBCC REINDEX. See an example on http://www.simple-talk.com/community/blogs/andras/archive/2007/09/26/37634.aspx
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
If you have the disk space, then it might be an idea to not shrink the log. Then it won't have to grow again, which adds to the time of your re-indexing. 😉
--------------------
Colt 45 - the original point and click interface
-
Also, do not shrink the database. Shrinking a database after doing a re-index does 2 things:
1. It gives you bad NTFS file fragmentation that harms your performance. This can only be cured by a NTFS defrag, an index rebuild does not touch this problem.
2. Almost all the benefits of your index rebuild are undone. The index rebuild of a cluster will re-order your table so all the extents are physically stored in cluster-index order. This improves disk read performance. Shrinking your database will move the extent nearest the end of your table to the first available bit of free space in the file, and so on with the rest of your data. You end up with your table out of sequence in your data file which harms disk read performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply