This is a quick blog post to let you know about a bug in SQL Server 2012 SP1 that can cause data loss when performing index maintenance.
The data loss issue can happen in some circumstances when you do a parallel online rebuild of a clustered index while there are concurrent data modifications happening on the table AND you also hit a deadlock and another error. Nasty when it occurs, but that should hopefully be a rare combination.
The workaround is to limit the online rebuild operation to be single threaded using the WITH (MAXDOP = 1) option.
There is a hotfix available – see KB 2969896 for more details.
Depending on which build you are on of SQL Server 2012 or 2014, the best option for you will vary. See my friend Aaron Bertrand’s post for comprehensive details.
14 thoughts on “Online index rebuild corruption bug in SQL Server 2012 SP1”
Paul
Does the hot fix has already been included in CU9 or CU10 ?
Ishtiaq
It’s not in CU9 or CU10.
Is this fixed in SQL Server 2012 SP2?
Unknown.
It doesn’t look like KB 2969896 is in SQL Server 2012 SP2. (http://support.microsoft.com/kb/2958429)
Ironic, both this KB and SP2 on the same day.
This is not fixed in SP2 (11.0.5058). And if you apply the SP1 hotfix and then apply SP2, you will lose the benefit of the hotfix. For SP2 you will need to wait for the first cumulative update (currently scheduled for end of July).
Would this get picked up by an integrity check? I am assuming that non clustered indexes at least would report failure as their clustered index entry would be missing.
Thanks.
Yes, it would show up as extra rows in the nonclustered indexes.
That’s a relief. I was getting flashbacks to your April Fools post for a minute.
This scenario can occur in 2012 RTM, 2012 SP1 (without the hotfix), 2012 SP2, and 2014.
There won’t be a fix for 2012 RTM, and fixes for 2012 SP2 and 2014 are forthcoming.
While the likelihood of this occurring is rather low, the risk of data loss or corruption is a pretty big deal, and I’ve laid out options and alternatives here:
http://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds
Is there any idea what the definition of ‘many rows’ might be? Should we assume that it is purely > 1?
I haven’t heard anything definitive, but enough rows to be able to run in parallel.
We’ve been doing rebuilds with MAXDOP 1 for some time so aren’t exposed to this. This reason we moved to MAXDOP 1 was because of our use of GUIDs. An ONLINE REBUILD made the fragmentation worse using SQL 2K12 SP1.
another interesting issue with online rebuilding found here…