In Microsoft SQL 2008 and onward versions, whenever SQL Administrators try to run a complex UPDATE statement coupled with NOLOCK hint against a table in MS SQL server, it may result in non clustered index corruptuion. This error is accompanied with the following error message, which explains that the SQL server error is logged for non-clustered index corruption.
[accordion-item title=”Non-clustered Index corruption is displayed in the
<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 # * CPerIndexMetaQS::ErrorAbort – Index corruption
<Date><Time> spid # *
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.
The probable cause of SQL database index corruption is NOLOCK hint which causes the query to read the Table-values incorrectly or when the query reads the same values in the data multiple times.
Though regular patches from Microsoft on SQL software are equipped to resolve these impending issues but the solution is not as reliable. Reason being the issue is hurting the client sentiments in the current scenario while the patches may be available in current or next or maybe still later issues.
Like all other issues, it was easier to handle non-clustered corruption in SQL server in comparison to SQL data-page as the index-created are redundant to the actual data page.
Non-clustered index corruption can be dealt with rebuild option. Rebuild using the following steps:
alter index IX_EmailAddress_EmailAddress
Sometime rebuild option is not the only solution. In that case, drop and recreate the index is a better solution using the options SSMS. Right-Click on the non-clustered index and select Script Index as DROP and CREATE to New Query Window
Verify that the Drop-Create service has resolved the issue. Run the command DBCC CHECKDB
— run DBCC CHECKDB to ensure the database is back to normal
DBCC CHECKDB (‘DatabaseName’) with NO_INFOMSGS
Ultimate Solution for Non-Clustered Index Corruption
While drop-recreate index may resolve the error, there are possibilities that corruption attacks again or corruption is not cured to give appropriate results.
Relying on Stellar Repair for MS SQL is the best solution. Try the following few steps and you can easily repair corrupt SQL database in the non-clustered index.
Steps to resolve non-clustered 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 at-ease feel as you can easily select or find .MDF file
Select database option works when you know the location of the database else uses the find option to search the location across the Drives.
Click on Repair button to start repairing process
After complete scanning, you are provided with a preview of the file, 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.
Though the preferred option is to save the database in MDF file format, if needed, save in multiple formats – CSV, HTML, and XLS.
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.
By this way, you can repair the non-clustered index corruption in SQL database.
Non clustered index corruption is the common issue in SQL database and the user can detect it easily when the entry in the Index ID is greater than 1. Though it is repairable with drop and recreate option, sometimes, the data corruption is high enough so, in this case, Stellar Repair for MS SQL becomes very helpful. This tool helps you in conducting the repair process in few simple steps.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.