A closer look at everything related to SQL Server

There is a SQL 2012 bug found (http://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds) when index rebuilt is done with ONLINE option.

A related KB article (http://support.microsoft.com/kb/2969896) states that:
The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows. For example, a replication updates many rows on a table when the online index build is running on the same table concurrently.

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.

Note To work around this issue, run the online index build in serial mode (DOP = 1).

According to the article link I pasted above, SQL server 2012 SP2 is out now. But fix for this bug is not included because it was discovered recently. So it suggests that you DO NOT apply SP2 until CU for this bug is available.
In the meantime, we have these options.
1. Add maxdop = 1 in the index rebuild query with ONLINE
OR
2. Run defrag with ONLINE = OFF
OR
3. Apply hotfix (http://support.microsoft.com/kb/2969896) available for sql server 2012 SP1. As with any hotfix, they are not tested thoroughly and can introduce new bugs.
OR
4. Wait for SP2 CU2, targeted to release by end of July and apply both of them together to SQL server 2012 SP1.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: