How to handle non clustered index corruption in SQL database

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

following manner:” state=open]

<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 # *

[/accordion-item]

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.

Causes

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.

Resolution Steps

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

on Person.EmailAddress

rebuild

go

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.

free download

Steps to resolve non-clustered index corruption in SQL database

  1. 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.
  2. Launch the software and open the application.
  3. You will come across the main interface, designed to give you at-ease feel as you can easily select or find .MDF file

  1. Select database option works when you know the location of the database else uses the find option to search the location across the Drives.
  2. Click on Repair button to start repairing process
  3. 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.
  1. Register the software and proceed to save the repaired database.
  2. Though the preferred option is to save the database in MDF file format, if needed, save in multiple formats – CSV, HTML, and XLS.
  3. 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.

To Summarize

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.

Comments(10)
  1. Peter November 22, 2019
    • Eric Simson November 22, 2019
  2. Stephen C. Davis December 27, 2018
    • Eric Simson December 27, 2018
  3. William Thomas September 5, 2018
  4. Gerardo Robinson November 27, 2017
    • Eric Simson November 28, 2017
  5. Ceaser Robinson October 26, 2017
    • Eric Simson October 27, 2017
  6. Jayaraj October 16, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.