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

Richard Douglas, 2014-06-12 (first published: 2014-06-10)

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads