SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data loss in clustered index occurs when you run online build index in SQL Server 2012

Data LossScared? Don’t worry, there is a workaround and a hotfix. More on that in a moment.

This morning I checked my Twitter feed and saw this post from Nicholas Cain, it’s really important to take note of this so I decided to write a few lines in the hope that more people would see this and take action.

The following is from Article ID: 2969896 – Data loss in clustered index occurs when you run online build index in SQL Server 2012

Symptoms

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:

  • The online index build query runs in parallel mode.
  • 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).

Action Points

So what Microsoft are suggesting is that by setting DOP=1 you will only use one thread to process the rebuild rather than multiple threads.

How do you do this? Here’s an example:

ALTER INDEX <idxname> ON <objectname> REBUILD WITH (ONLINE=ON, MAXDOP=1);

Now if you can’t download the hotfix (which you can find from the KB here) because of policies that stop you from implementing them then you need to take a look at the code you have for your index rebuilds and make the necessary changes.

Using Maintenance Plans which doesn’t provide an option to rebuild upon how fragmented each index is? My advice – stop using a sledgehammer and find a good well tested community script that will only rebuild and reorganise those indices that need to be defragmented. The other problem with maintenance plans is that you cannot set DOP, not via the UI anyway.

Maintenance Plan Index Rebuild

Alternatively, you could look at the script (as shown in the image above) and then copy it into a T-SQL task with the DOP=1 option. Please be cognizant of the fact that this will then become a static list and any index you may add will not be picked up. The other option is to have a statement setting maxdop to 1 for the instance and setting it back when the plan ends, this approach would however affect every other query that would normally use parallelism during this time unless it explicitly had a DOP parameter set for it.

To set MAXXDOP at the instance level you enter code similar to the code shown below:

EXEC sys.sp_configure N’max degree of parallelism’, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO

 

Consider this a wake up call and pay close attention to how long your maintenance window will now last.

Richard Douglas

Richard Douglas is a SQL Server Professional working in the UK where he recently worked as a DBA in women's clothing, not literally he hastens to add! He is certified in SQL Server 2008, runs the Maidenhead SQL Server User Group Pass Chapter and is on the organising committee for SQL Relay. In his spare time plays the trumpet in local symphony orchestras.

His online presence includes:

Comments

Leave a comment on the original post [sql.richarddouglas.co.uk, opens in a new window]

Loading comments...