May 17, 2012 at 7:44 am
Hi All
A question regarding Log Shipping and Index maintenance
Server A - Log Shipping Primary
Server B - Log Shipping Secondary
If I rebuild Indexes on Server A, does that mean that the same Indexes are Rebuilt on ServerB when the logs are restored?
Thanks
May 17, 2012 at 7:45 am
Yes, the index rebuild is logged to the transaction log and therefore replayed on the subsequent restore.
May 17, 2012 at 7:46 am
Yes. Index maintenance is just like any other transaction, so it is logged and gets applied to the target.
May 17, 2012 at 7:49 am
Michael Valentine Jones (5/17/2012)
Yes. Index maintenance is just like any other transaction, so it is logged and gets applied to the target.
Ok
Why is it that when the logs are restored onto the Secondary, it doesn't take nearly as long as the Index Rebuild took on the primary?
Example
>> Rebuild 1 Index on Primary - 10 mins
>> Start LS Log Backup Job - 2 mins
>> Start Restore - 5 mins
May 17, 2012 at 7:53 am
secondary server is generally in norecovery mode so no users, so no load on the database so can usually do things quicker as its not serving user requests
May 17, 2012 at 7:56 am
anthony.green (5/17/2012)
secondary server is generally in norecovery mode so no users, so no load on the database so can usually do things quicker as its not serving user requests
Ok
So if I ran a sp_who2 command while the restore job is running on the Secondary, I would see "Alter Index" statements coming through?
May 17, 2012 at 7:58 am
Unsure if you will see the alter index, I would of said you would see restore database or restore log as its restoring the log, it just so happens that the log says to alter index.
I would suggest rebuild and index and take a look and let us know.
May 17, 2012 at 8:12 am
anthony.green (5/17/2012)
Unsure if you will see the alter index, I would of said you would see restore database or restore log as its restoring the log, it just so happens that the log says to alter index.I would suggest rebuild and index and take a look and let us know.
Sounds good
Will test
May 17, 2012 at 8:25 am
anthony.green (5/17/2012)
Yes, the index rebuild is logged to the transaction log and therefore replayed on the subsequent restore.
Am I correct in that when the Log Shipping Primary is running in bulk_logged recovery, this doesn't apply
?
Thanks
May 17, 2012 at 8:29 am
No, as an index rebuild is a fully logged operation so it logs the same amount in Full or Bulk-Logged so there is no difference
May 17, 2012 at 12:26 pm
derekr 43208 (5/17/2012)
anthony.green (5/17/2012)
secondary server is generally in norecovery mode so no users, so no load on the database so can usually do things quicker as its not serving user requestsOk
So if I ran a sp_who2 command while the restore job is running on the Secondary, I would see "Alter Index" statements coming through?
No, you would not see alter index on the secondary. A log restored on the secondary does not replay the SQL statements issued on the primary database. It replays the changed database pages on the secondary database, i.e. the net effect of the SQL statements run on the primary database. A subtle difference in terms of wording but a huge difference in what is actually happening.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 12:30 pm
anthony.green (5/17/2012)
No, as an index rebuild is a fully logged operation so it logs the same amount in Full or Bulk-Logged so there is no difference
Slight tweak here. The index rebuild operation can be minimally logged so you save a little I/O during the rebuild itself, however the size of the resulting transaction log backup file will be unchanged regardless of whether in full or bulk-logged.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 12:45 pm
opc.three (5/17/2012)
anthony.green (5/17/2012)
No, as an index rebuild is a fully logged operation so it logs the same amount in Full or Bulk-Logged so there is no differenceSlight tweak here. The index rebuild operation can be minimally logged so you save a little I/O during the rebuild itself, however the size of the resulting transaction log backup file will be unchanged regardless of whether in full or bulk-logged.
however the size of the resulting transaction log backup file will be unchanged
Is this a certainty, regardless of Fill Factor or any other circumstance?
Example:
>> Database is in Full Recovery
>> Rebuild one tables's Indexes
>> Take Log backup
>>> Change recovery to bulk_logged
>> Rebuild same table's indexes
>> Take log backup
In this scenario(with the above being the ONLY activity on the database, would the Log Backup sizes be the same?
Thanks
May 17, 2012 at 12:57 pm
derekr 43208 (5/17/2012)
opc.three (5/17/2012)
anthony.green (5/17/2012)
No, as an index rebuild is a fully logged operation so it logs the same amount in Full or Bulk-Logged so there is no differenceSlight tweak here. The index rebuild operation can be minimally logged so you save a little I/O during the rebuild itself, however the size of the resulting transaction log backup file will be unchanged regardless of whether in full or bulk-logged.
however the size of the resulting transaction log backup file will be unchanged
Is this a certainty, regardless of Fill Factor or any other circumstance?
Example:
>> Database is in Full Recovery
>> Rebuild one tables's Indexes
>> Take Log backup
>>> Change recovery to bulk_logged
>> Rebuild same table's indexes
>> Take log backup
In this scenario(with the above being the ONLY activity on the database, would the Log Backup sizes be the same?
Thanks
Pretty much, yes. Bulk-logged saves you writing all new or changed data and instead allows us to only write page allocations (loose definition, see Gail's article below for deeper dive). However, when taking a log backup the contents of those pages must be read from the data file and written to the log backup. No way around it. How else would the data in those extents get into the log backup and over to the secondary :Whistling:
These articles explain it well:
An examination of bulk-logged recovery model by Gail Shaw[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply