Tuesday, April 25, 2017
 
SQL Server Alerts and Notifications
 
     

SQL Alert - Index Rebuild Can Cause Data Corruption

By Saleem Hakani on 8/25/2014

Published: Tuesday, June 10th 2014

The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows.

Example Scenarios:

1.Replication updates many rows on a table when the online index build is running on the same table concurrently.

2.Heavy DML activity happening on the table while index is being rebuilt

This issue occurs when the following conditions are true:

1.The online index build query runs in parallel mode.

2.Deadlock error and a fatal error such as "lock timeout" occur in a specific order.

Workaround:

1.Use MAXDOP = 1 when Rebuilding Indexes or use the community script SQLOPS_DeFragger with (@maxdop=1) (Click Here)

2.Apply the Supported Hotfix

More Information about this alert can be found @ https://support.microsoft.com/kb/2969896

Alter Index
Bug with Index Rebuild
Online Index Creation
Remove Index Fragmentation
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM