BT
x Your opinion matters! Please fill in the InfoQ Survey about your reading habits!

Potential for Data Loss in SQL Server 2012 SP 2

by Jonathan Allen on Jun 12, 2014 |

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.

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

A recent CU will include the hotfix. by Qingsong Yao

The SQL servicing model is such that we port all hotfixes forward according to the patch cycle for each product. So, this fix should be included in the next (first) CU for SQL11 SP2. I imagine that the fix for SP1 was released during the final stages of the SP2 release cycle when no new changes were being accepted. There are several fixes in the most recent CU for SP1 which are not yet in SP2, but will be in the first CU for SP2. It is only in the rarest of cases that a patch will be released for all products concurrently, and those only for severe security-related issues.

I asked our service engineer and he said "This is already slated for inclusion in SQL11 SP2 CU1. "

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

1 Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2014 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT