October 24, 2007 at 1:28 pm
Comments posted to this topic are about the item Which recovery model would have the largest transaction...
November 30, 2007 at 7:04 pm
I've noticed that if you DBCC REINDEX a large, heavily indexed database in full recovery mode, the transaction log backup can be larger than a full backup. This is because of the transaction log contains entries for "delete this index entry" and "insert this index entry" for each index times each row. This can actually be larger than simply dumping all the active pages in the database.
If this was in bulk-logged mode, it would probably create a smaller log backup file, because an extent of index entries is probably smaller than two index modification transaction log entries for the same number of values.
If you BULK INSERT to add 1% of records scattered across the data and index pages, then would create a larger log backup file, because each transaction entry is much smaller than a database extent (8 pages).
Just one more example where "your milage may vary depending on your driving habits."
David Lathrop
DBA
WA Dept of Health
December 1, 2007 at 11:49 am
Good points.
December 1, 2007 at 12:11 pm
thanks for bringing up that. good tips
October 11, 2012 at 7:25 am
wrong answer.... 🙁
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply