Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.