When I hear the discussion about causes of data corruption in SQL Server, I always mention bugs in the operating system and the software itself. Fortunately, the last mentioned reason is an extremely rare case, but people are bound to make mistakes and SQL Server is also developed by humans. One such case occurred recently. It is related to new versions of program products: SQL Server 2012 and 2014. To be short, when you rebuild indexes online in the mentioned above products it may cause damage or loss of index data. It happens if the queries updating multiply database rows are executed in parallel and a deadlock error and fatal error, such as «lock timeout», occur in a specific order. The problem is quite serious, that’s why it is necessary to pay focused attention to it and install released updates. Detailed description and a link to download the hotfix are ??available via the link below. The hotfix is available only for SQL Server 2012 SP1 and SP2, and SQL Server 2014. Hotfix for Server 2012 RTM doesn’t exist and is not even in view.
It is necessary to notice that the recently released SP2 for SQL Server 2012 does not contain the hotfix. Therefore, if you get used to install only service packs and ignore the cumulative update or specific hotfix you may face a big problem. In my view, the situation when organizations start using a new version of the product the moment when only the first or second service pack becomes available doesn’t make sense. Yes, I think we should not rush headlong and update the server as soon as a new version of software is released, but to wait long is a bad decision as well. Now the world is changing rapidly and release cycle is becoming shorter, as in the case of SQL Server 2012 and 2014. The second service pack for SQL Server 2012 was released after the official release of SQL Server 2014, and the third, I fear, will not be released at all. But let’s go a little back to the main issue. How to check that you have all the necessary hotfixes installed? To do this, run the following query
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
on your server and check the result against the information in the table below:
SQL Server 2012 RTM | No available updates for this version. It is recommended to install the first or second service pack and then hotfix KB #2969896. |
SQL Server 2012 SP1 | If the version is earlier than 11.0.3437 install the hotfix KB #2969896. |
SQL Server 2012 SP2 | If the version is earlier than 11.0.5522 install the hotfix KB #2969896. |
SQL Server 2014 RTM | If the version is earlier than 12.0.2370 install the hotfix KB #2969896 or second cumulative hotfix package KB # 2967546. |
If there are any reasons preventing you from installing the update today, for example, you need to carry out a full cycle of testing in-house first, you can temporarily solve the problem in a number of ways:
- You can temporarily stop index rebuilding process.
- You can set the option max degree of parallelism at the server level to 1, but note that this can negatively affect the performance of other queries.
- You can add the option WITH (MAXDOP = 1) to all queries rebuilding indexes. If you use Maintenance Plant there is no possibility to indicate that indexes rebuilding must be run in single-threaded mode. If you use other utilities you should look into the documentation to find out whether rebuilding in single-threaded mode is available.
