Q

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

This was first published in February 2004

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

This Content Component encountered an error
Close