Potential for Data Loss in SQL Server 2012 SP 2
The second service pack for SQL Server 2012 was recently released with over 30 bug fixes since the last cumulative update. But according to Aaron Bertrand of SQL Sentry, one important hotfix for Enterprise customers didn’t make the cut.
According to Microsoft, the bug in question is triggered when,
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.
The fix for this bug is currently only available as a “Hotfix” for SQL Server 2012 SP 1. In Microsoft nomenclature, a hotfix is meant only for people who are actually experiencing the problem described. It has not been as thoroughly tested as a normal update and thus may cause other problems.
In the past, administrators were expected to request specific hotfixes from Microsoft by opening a support ticket and citing the Knowledge Base ID for the fix along with the symptoms they observed. If Microsoft’s helpdesk determined the hotfix was appropriate, they would refund the cost for the support ticket. These days many hotfixes are freely available, but with the aforementioned warnings.
This particular hotfix is only applicable to SQL Server 2012 SP 1. If you are using SQL Server 2012 SP 2 or SQL Server 2014 then you will have to use one of the work-arounds described by Aaron Bertrand. Briefly, these workarounds involve setting the index rebuild operation to serial model (MAXDOP = 1) instead of parallel mode. The way you do this depends on what tools you are using to perform the operation. Aaron covers basic several methods including Ola Hallengren's index maintenance solution, SQL Sentry Fragmentation Manager, and the standard Maintenance Plans.
A recent CU will include the hotfix.
I asked our service engineer and he said "This is already slated for inclusion in SQL11 SP2 CU1. "
Yoni Goldberg Oct 30, 2014
Dmytro Svarytsevych Oct 30, 2014