BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News Potential for Data Loss in SQL Server 2012 SP 2

Potential for Data Loss in SQL Server 2012 SP 2

Bookmarks

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.

Rate this Article

Adoption
Style

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.

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

Community comments

  • A recent CU will include the hotfix.

    by Qingsong Yao,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

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

BT