Summary: This post discusses the ‘SQL Server nonclustered’ corruption issue and what causes it. It also advises on how to repair non-clustered index manually or by using a SQL repair tool.
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.
Non-clustered Index corruption is displayed in the following manner:
<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 # *
Note: NOLOCK hint can be applied to the Source Tables in a statement but not to the Target Tables in a statement.
Once the non-clustered index corruption is detected, it is mandatory to find the cause of the issue.
What Causes SQL Server Nonclustered Index Corruption?
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.
How to Fix Corruption in Nonclustered Index?
Although regular patches from Microsoft on SQL software are rolled out to resolve the non-clustered index corruption issue, the solution is not that reliable.
If the patches don’t work, follow the steps in the same sequence as below to fix non-clustered index corruption manually:
Step 1: Rebuild Index in SQL Database
Note: You cannot rebuild an index if the filegroup where it is located is offline or set to read-only.
Run the ALTER INDEX REBUILD command to rebuild the non-clustered index by executing the following T-SQL query:
alter index IX_EmailAddress_EmailAddress
on Person.EmailAddress
rebuild
go
The above query rebuilds the ‘IX_EmailAddress_EmailAddress’ index on the ‘Person.EmailAddress’ table.
Downside to Rebuilding a Non Clustered Index
Rebuilding reads an old index to create a new one. New index will have missing rows as old index.
Step 2: Drop and Recreate Index
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:
- In SSMS, click to expand a database under Databases.
- Right-click on a database table, and click Script Table as > DROP And CREATE To > New Query Editor Window.
Downside of Using Drop-recreate Index Method
While drop-recreate index may resolve the error, there are possibilities that corruption attacks again or corruption is not cured to give appropriate results.
Step 3: Run DBCC CHECKDB
Run the following DBCC CHECKDB command to ensure the database is back to normal:
DBCC CHECKDB ('DatabaseName') with NO_INFOMSGS
If these steps fail to fix the nonclustered index corruption issue, using a SQL repair tool may help.
Alternate Solution to Fix SQL Server Nonclustered Index Corruption
Use Stellar Repair for MS SQL software to repair corrupt SQL database and fix corruption in nonclustered index. The software is designed to repair the SQL database for non-clustered index and clustered index corruption.
Steps to Resolve Nonclustered Index Corruption in SQL Database
- Stellar Repair for MS SQL software is designed to repair the SQL database for non-clustered index and the clustered index corruption. The software uses powerful algorithms to scan thoroughly and recover as much data.
- Launch the software and open the application.
- You will come across the main interface, designed to give you an at-ease feel as you can easily Browse or Search an .MDF file
- Click Repair to start the repairing process
- Once the scanning is complete, a preview window with all the repairable database objects opens. Search through the scanned preview to verify that the database is complete and does not show any signs of corruption.
- Register the software and proceed to save the repaired database
- Click Save on the File menu.
- In the ‘Save Database’ dialog box, do the following;
- Save the repaired file in multiple formats – MDF, CSV, HTML, and XLS.
- Next, save the repaired file in New Database or Live Database.
Note: If the database was too corrupt, it is advisable to save it as a New Database, and small corruptions can be repaired and saved in Live Database. Both options are available on this tool. Choose as per your requirement.
- Hit the Save button.
The non-clustered indexes in your SQL database will be repaired.
To Summarize
Non clustered index corruption is a common issue in SQL database, and a user can detect it easily when the entry in the Index ID is greater than 1. Although it is repairable using the drop and recreate option, you may fail to recover all the data. Use Stellar Repair for MS SQL to repair the corrupt indexes in your database table without any data loss.
