February 12, 2006 at 3:19 am
Inside SQL Server 2000 states the following:
In FULL recovery mode, SQL Server will also fully log CREATE INDEX operations. In SQL Server 2000, when you restore from a transaction log backup that includes index creations, the recovery operation is much faster because the index does not have to be rebuilt—all the index pages have been captured as part of the database backup.
However, isn't this more like bulk-logged mode in which the pages identified as changed by the bulk-changed map are captured as part of the log backup. Won't this capture of index pages slow down the log backup process? Please clarify.
February 13, 2006 at 5:54 am
If you look at the entire quote from Inside SQL Server 2000, it goes on to say "In previous versions, SQLServer only logged the fact that an index had been rebuilt, [and not the index pages themselves] so when you restored from a log backup the entire index would have to be rebuilt all over again."
So, the explanation is that more pages are written to the log backup now since they also include any index pages that might have been generated. This will increase the size of the tran log backup file, and yes, it will also increase the time it takes to write it, but nothing compared to the time it takes to rebuild large indexes. In fact, Kalen Delaney makes exactly that statement in the next paragraph.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply