Data corruption in SQL Server 2012 and 2014 when rebuilding indexes online
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:
.png)
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.
Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.
MoreSubject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:
MoreIn our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.
MoreSubscribe to our blog
Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.
Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:
Discuss your project with us