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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top