{"id":11873,"date":"2017-10-13T05:55:19","date_gmt":"2017-10-13T05:55:19","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=11873"},"modified":"2023-09-20T12:07:14","modified_gmt":"2023-09-20T12:07:14","slug":"handle-non-clustered-index-corruption-in-sql-database","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/handle-non-clustered-index-corruption-in-sql-database\/","title":{"rendered":"How to Handle Nonclustered Index Corruption in SQL Database"},"content":{"rendered":"

In Microsoft SQL 2008 and onward versions, whenever SQL Administrators try to run a complex UPDATE statement coupled with NOLOCK hint against MS SQL server table, it may result in non-clustered index corruption. The error message below is logged in SQL Server error log when corruption in non-clustered index occurs.<\/p>

Non-clustered Index corruption is displayed in the<\/strong> following manner:<\/strong><\/p>

<Date><Time> spid # Error: 8646, Severity: 21, State: 1.
<Date><Time> spid # Unable to find index entry in index ID 3, of table 2102402659, in database ‘<DatabaseName>’. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.
<Date><Time> spid # Using ‘dbghelp.dll’ version ‘4.0.5’
<Date><Time> spid # **Dump thread – spid = 0, EC = 0x0000000XX000000
<Date><Time> spid # ***Stack Dump being sent to Y:\\MSSQL\\MSSQL10.MSSQLSERVER\\MSSQL\\LOG\\SQLDump0000.txt
<Date><Time> spid * 
*******************************************************************
<Date><Time> spid # *
<Date><Time> spid # * BEGIN STACK DUMP:
<Date><Time> spid # * <Date><Time> spid#
<Date><Time> spid # *
<Date><Time> spid # * CPerIndexMetaQS::ErrorAbort – Index corruption
<Date><Time> spid # *<\/p>

\n

Note: <\/strong>NOLOCK hint can be applied to the Source Tables in a statement but not to the Target Tables in a statement.<\/em><\/p>\n<\/blockquote>

Once the non-clustered index corruption is detected, it is mandatory to find the cause of the issue.<\/p>

What Causes SQL Server Nonclustered Index Corruption?<\/strong><\/strong><\/h2>

The probable cause of SQL database index corruption is NOLOCK hint, which causes the query to read a table value incorrectly, or when the query reads the same values in the data multiple times.<\/p>

How to Fix Corruption in Nonclustered Index?<\/strong><\/strong><\/h2>

Although regular patches<\/a> from Microsoft on SQL software are rolled out to resolve the non-clustered index corruption issue, the solution is not that reliable.<\/p>

If the patches don’t work, follow the steps in the same sequence as below to fix non-clustered index corruption manually:<\/p>

Step 1: Rebuild Index in SQL Database<\/strong><\/h3>

Note:<\/strong><\/em> You cannot rebuild an index if the filegroup where it is located is offline or set to read-only.<\/em><\/em><\/p>

Run the ALTER INDEX REBUILD command to rebuild the non-clustered index by executing the following T-SQL query:<\/p>

alter index IX_EmailAddress_EmailAddress<\/span>
on Person.EmailAddress<\/span>
rebuild<\/span>
go<\/span><\/pre>
The above query rebuilds the ‘IX_EmailAddress_EmailAddress<\/em>’ index on the ‘Person.EmailAddress’ table.<\/p>
Downside to Rebuilding a Non Clustered Index<\/strong><\/p>
Rebuilding reads an old index to create a new one. New index will have missing rows as old index.<\/p>
Step 2: Drop and Recreate Index<\/strong><\/strong><\/h3>
You can also try to drop and recreate the corrupt non-clustered index using SQL Server Management Studio (SSMS). To do so, follow these steps:<\/p>