Ask the Expert

Log shipping gets out of sync on one database with re-index

We have a log shipping job that runs fine, except that when we run dbcc re-index on the publisher, the subscriber gets out of sync. This only occurs on one of many databases we ship, but it is the largest. The backup occurs and the file is shipped. How do we avoid this and keep the indexes up-to-date?

    Requires Free Membership to View

One of the other options you have is indexdefrag. A reindex will rebuild the entire index in a single transaction that locks the table and only commits once it is done. If you are rebuilding the clustered index, all non-clustered indexes will rebuild since they point at the clustered index. There isn't anyway around it using a reindex since log shipping can only apply committed transactions and a tran log backup will only take up to the oldest open transaction. An indexdefrag rebuilds the indexes as well using a series of "micro-transactions" which allow for the terminate of the indexdefrag without losing any work performed and it also will move the data to your secondary much more quickly, thereby reducing the latency.

 

For More Information

This was first published in February 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: