Data Corruption is bad. Corruption either occur physically or logically in indexes. The Non-clustered Indexes are pointers of data that helps you in finding data more quickly. It contains Non-clustered key value and each key value has a pointer that contains the key value of data rows.Sadly, the ones who work in SQL Server is not aware about the processing behind it. The error of Non clustered Index is shown as:
<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 ‘'. 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.
Cause of Corruption in Non-Clustered Index
Index corruption occurs due to the NOLOCK hint run with any DML operation.This causes the query to read same value multiple times or read the table value incorrectly.
Sometimes it’s only the Non-clustered indexes that get corrupted, so you don’t have to run any restore operation on backups or run any repair command. Non- Clustered corruption is quite easy to handle, unlike, corruption in SQL data pages.You will able to fix it by running DROP and CREATE index.
How to Find Corrupted Indexes?
The consistency checks on indexes differ on the level of its compatibility of databases.
- If the compatibility level <=100;
- DBCC CHECKTABLE performs physical and logical consistency checks on all Non-clustered indexes and on the single table. By default, only physical consistency checks are done on XML Indexes, spatial indexes and indexed views.
- In WITH EXTENDED_LOGICAL_CHECKS, logical consistency checks are performed on an indexed view, XML indexes, and spatial indexes. Only logical consistency checks are performed, if NOINDEX is specified.
Note: Running WITH EXTENDED_LOGICAL_CHECKS can highly affect the performance, therefore, you must use it only when:
- If you have issues related to Suspect index that are not related to physical corruption.
- Page level checksum is turned off.
- You suspect column- level hardware corruption.
- If compatibility level >=90;
- DBCC CHECKTABLE performs both physical and logical consistency check on a single table, indexed view and on all non clustered indexes. Spatial Indexes are not supported.
How to Fix it Manually
- When nonclustered index is corrupted, the first step is to Run REPAIR_REBUILD command when the user is in SINGLE Mode.
- If Rebuild is not possible, then you need Drop & Create. Follow below procedure:
- Run DBCC CHECKDB command to check consistency of the database.
Alter index IX_EmailAddress_EmailAddress
The corruption FIXATION is not guarenteed because Rebuild reads the old index to build new one as a result of this new index contain the missing rows as old ones.
Right click on Indexes -> Select Script Index as DROP & CREATE to New query Window.
Easy way to Repair Corruption By an Alternate solution
If you don’t want to perform lengthy process and want to save your time in repairing indexes, you can use professional third party SQL Recovery tool. The manual process is bit time consuming and may or maynot solve your problem. So you can use third party tool for an easy repairing. SQL Recovery software is capable of repairing corrupted indexes with an ease and fix your repaired indexes as a solution.
In this Blog, I have discussed about the solution to recover Non-clustered index in SQL Server Database. The blog covers two solution of repairing non clustered indexes. Choose the repair solution as per your ease.