SQL SERVER – Too many dumps generated. CPerIndexMetaQS::ErrorAbort – Index Corruption

I always try to share my knowledge and learning from a client engagement. This helps me in remembering the steps I took to reach to the solution. It also helps my blog readers to follow the same steps and fix their problem rather than waiting for some expert to fix it. So you see, this is a WIN-WIN situation for both of us. Let us learn in this blog post how to fix index corruption. Before you start reading this article, I suggest you read my previous article here: SQL SERVER – 5 Don’ts When Database Corruption is Detected.

Recently, one of the blog readers read my below blog and contacted me for assistance.

SQL SERVER – Too Many SQLDump Files Consuming a Lot of Disk Space. What Should You Do?

I asked him to share SQL Server ERRORLOG to understand the cause of dumps.

***Stack Dump being sent to D:\SYSDATA\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0870.txt
* *******************************************************************************
* BEGIN STACK DUMP:
* 12/12/17 15:00:17 spid 82
* CPerIndexMetaQS::ErrorAbort – Index corruption

As soon as I saw above message, it became clear that there is an index corruption in any of the database. Now we need to figure out which index it is and how bad the situation is. When I searched on the internet, I would that there are some bugs with Microsoft SQL Server product which can cause this kind of corruption.

FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server

FIX: “CPerIndexMetaQS::ErrorAbort – Index corruption” error when you try to update a partitioned view in SQL Server 2008 R2

My client was on the latest version, so the fix was already available.

The next action is to find which database, which table and which index. I asked them to run “DBCC CHECKDB” on all databases. They informed me that they have run it already, but don’t have the output saved. Thanks to SQL Server where it writes the information in the SQL Server ERRORLOG.

* BEGIN STACK DUMP:
* 11/01/16 14:56:40 spid 91
* DBCC database corruption
* Input Buffer 98 bytes –
* dbcc checkdb (‘ERP’)

In the above case, we had a table name in the ERRORLOG.

WORKAROUND/SOLUTION

There are various methods to find the corruption. We should always start with checkdb. If there is a particular table having problem, then start with checktable for that particular table.  Few things which I learned while working with this client are:

1. Whenever checkdb or checktable is completed (clean or error), it writes messages in the ERRORLOG like below:

DBCC CHECKTABLE (DB.dbo.MyTable) executed by SQLAuth\Pinal found XX errors and repaired XX errors. Elapsed time: XX hours XX minutes XX3 seconds.

2. SQL Server also writes the last checkdb information in the header of the database file. This is printed in the ERRORLOG when the database is restarted (either by offline-online method or SQL Server restart).
3. While looking at the output of CHECKDB/CHECKTABLE, you need to look at the second last line of output which says what was the overall outcome.
   
   Msg 8951, Level 16, State 1, Line 3
   Table error: table ‘ERP.PLANT_HISTORY’ (ID 1505608657). Data row does not have a matching index row in the index ‘IDX_PLANT_MOVEMENT’ (ID 4). Possible missing or invalid keys for the index row matching:
   Msg 8955, Level 16, State 1, Line 3
   Data row (210:383884:46) identified by (PLANT_ID = 601039677) with index values ‘PLANT_ID = 20171224’
   Msg 8952, Level 16, State 1, Line 3
   There are 93317587 rows in 92419 pages for object “ERP.PLANT_HISTORY”.
   CHECKTABLE found 0 allocation errors and 1 consistency errors in table ‘ERP.PLANT_HISTORY’ (object ID 1505608657).
   repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (ERP.ERP.PLANT_HISTORY).
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.
   

As we can see in the above output, CHECKTABLE was run on “PLANT_HISTORY” table and index id = 4 is having some issues. So, the best bet would be to drop and recreate the index. You can also do repair_rebuild but database needs to be in single_user mode to do that.

So, to summarize, here are the 2 options we have

1. Run DBCC with repair option. This needs the DB in single user mode

   ALTER DATABASE Database_Name SET SINGLE_USER
   GO
   DBCC CHECKTABLE('TABLE_NAME',REPAIR_REBUILD)
   GO

2. Drop and recreate the index. This does not need the complete DB to be offline / single user mode. Based on this client, rebuilding the index didn’t not help.

Any action which you can take in such errors is dependent on the output of DBCC CHECK* commands. I can’t stress enough that if you are not taking backups, you may need to have an unknown amount of data loss if you go with DBCC REPAIR_ALLOW_DATA_LOSS command.

Again, if you have never fixed error with your database backup and if you are not confident what to do next in this situation, I suggest you take Help from Expert before you take a step which may render your database totally unrecoverable. Remember, once you take one wrong step, there are chances that your database is of no use. 

Reference: Pinal Dave (https://blog.sqlauthority.com)