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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2004