Table of Content
    SQL Database Repair

    How to Handle Nonclustered Index Corruption in SQL Database

    Table of Content

      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

      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.
      Database option in SSMS
      • 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.

      free download

      Steps to Resolve Nonclustered 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 an at-ease feel as you can easily Browse or Search an .MDF file
      Search option for .MDF file in Stellar Repair for MS SQL
      1. Click Repair to start the repairing process
      2. 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.
      3. Register the software and proceed to save the repaired database
      1. Click Save on the File menu.
      Save Database option in File menu
      1. In the ‘Save Database’ dialog box, do the following;
        • Save the repaired file in multiple formats – MDF, CSVHTML, 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.

      1. Hit the Save button.
      repaired file saving options

      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.

      Was this article helpful?

      No NO

      About The Author

      Priyanka Chauhan linkdin

      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.


      1. I relish, lead tߋ I foᥙnd just ѡhat I uѕed tо be ⅼooking fοr. You haѵe ended my 4 daʏ lengthy hunts! God Bless you mаn. Have a nice dɑy.

      2. Our team faced SQL error 8646 while using the company database.

        To resolve this error we have used Stellar SQL tool which quickly repairs corrupted data without harm data integrity.

      3. I’m facing the same issue of data corruption in the non-clustered index. To sort out this issue I run REPAIR_REBUILD command to repair corrupt data. After the rebuild, the new index contains missing rows as old ones.

        Need Urgent Help!

      4. Thanks for the help, drop and create to new query window command worked like magic. Now, my Database is functioning perfectly.

      5. SQL 2008 has now become a threat to data security. I think migration on higher version is a good option for companies.

        1. The extended support deadline is 7/9/2019. So, users have enough time to switch on a higher version.

          Note: For database corruption-related issues, SQL users can use our software for lifetime.

      6. This is such a complex process. Never knew there is this much information involved for it. Yet to learn some programming to understand fully. But the logic is reasonable.

      Leave a comment

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

      Image Captcha
      Refresh Image Captcha

      Enter Captcha Here :

      Related Posts


      Why Choose Stellar?

      • 0M+


      • 0+

        Years of Excellence

      • 0+

        R&D Engineers

      • 0+


      • 0+


      • 0+

        Awards Received