How to Fix Nonclustered Index Corruption in SQL Server & Rebuild Them?

Summary: SQL users are aware of both clustered as well as non-clustered indexes in SQL databases. However, they might not be aware of the solution to fix Index corruption in SQL Server database. This article is dedicated to this query of users only.

Here, users will get to know the entire details of the error, methods to rebuild non clustered Index in SQL, causes & Microsoft’s solution for this. Mostly this error occurs in SQL Server version 2008 & later versions.

What is Nonclustered Index Corruption in SQL Server?

Well, SQL administrators are often required to run several commands based on their work requirements. Now, some of these commands might be complex. Here, the interesting part: When users run complex UPDATE commands with the NOLOCK hints for the SQL table, they are most likely to face corruption in nonclustered Indexes in the SQL database. Evidently, they must rebuild nonclustered index SQL Server to get the desired output.

Below we have mentioned the authentic showcase of the error for users in the system.

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

Here, users need to understand that it is possible to apply the NOLOCK hint to the Source Tables in an SQL statement. However, it is not allowed when it comes to the target tables.

Now, users must act fast & find the cause of this corruption as soon as possible. Then only, the rebuilding of nonclustered indexes can be executed.

Also Read: SQL Data Error Cyclic Redundancy Check Fixed

Need to Rebuild Non Clustered Index : Corruption Causes

SQL server query or command reads a table value falsely or multiple times creating an error. This is the primary issue that occurs in this nonclustered Index corruption in SQL Server is NOLOCK Hint.

Microsoft fixed this issue in the cumulative updates of SQL server which are:

• Cumulative Update 1 for SQL Server 2014 /en-us/help/2931693
• Cumulative Update 11 for SQL Server 2012 /en-us/help/2908007
• Cumulative Update 7 for SQL Server 2012 SP1 /en-us/help/2894115
• Cumulative update 13 for SQL Server 2008 SP3 /en-us/help/2880350
• Cumulative Update 9 for SQL Server 2008 R2 SP2 /en-us/help/2887606

Also Read: SQL Server Cannot Drop Database in Use Issue Resolved

However, before we proceed to rebuild nonclustered Index SQL Server, we must know all the causes including even the tiniest ones. These causes are:

• Faulty RAM/Memory Corruption: Corruption in the memory often results in damaging both the nonclustered & clustered indexes.

• Hardware and Software Errors: Hardware component issues like disk failures along with software bugs & glitches are also a factor.

• Server Updates or Migration: SQL Server migration & updates are common for users. However, failure here also causes irregular indexes.

• Power Outages/Shutdowns: Inappropriate shutdowns of the SQL Server & sudden power outages interrupt queries & cause corruption.

• Standard of Data Security: Executing INSERT, UPDATE, & DELETE commands while overrunning statements also contribute. The NOLOCK hint is there too.

• SQL DB Storage Errors: Lack of storage space & incorrect folder path can result in a similar result for nonclustered indexes of SQL DB.

• Various Cyber Attacks: Last but not least, viruses, malware, ransomware, different types of SQL injection, etc cause corrupt nonclustered indexes.

How to Fix Index Corruption in SQL Server with T-SQL Queries

Now, that we are well aware of each & every cause of this hassle, it’s time we start learning the solutions. Beginning with the manual ones, it’s obvious that Microsoft often releases update patches to fix this issue. However, sometimes the issues remain as it is. That’s when the T-SQL command method comes into action.

Make sure that the index filegroup is Online & not in Read-Only mode. Then users can proceed to rebuild nonclustered Index in the Server.

Manual Method 1. Run the ALTER INDEX REBUILD command as shown below:

alter index IX_EmailAddress_EmailAddress
on Person.EmailAddress
rebuild
go

Now, before the user starts doing this, they must understand that there is also a shortcoming to this. This shortcoming is that whenever users rebuild a nonclustered index, the database reads an old index to build a new one. Therefore, users might face missing rows.

Manual Method 2. Use SSMS to  DROP and RECREATE INDEX in SQL

Step 1. Open the SSMS in your system.

Step 2. Connect to your database & expand the desired database.

Step 3. Right Click >> Script Database as >> DROP & CREATE to >> New Query Editor Window.

Drawback: There are two drawbacks of this method as mentioned below:

• The corruption in nonclustered index might occur again.
• Corruption is not totally fixed to get the required output.

Final Check: Finally, users need to verify if their database is working normally or not. Run the following DBCC command:

DBCC CHECKDB ('NameofDatabase') with NO_INFOMSGS

Now, even if all of these manual solutions fail or users find them quite complex, they must go for the advanced automated tools.

Best Way to Handle Index Corruption in SQL Server Database

SysTools is a renowned brand for fixing SQL issues. Furthermore, their Best Rated SQL Recovery Tool is what experts praise a lot. In order to fix the corruption issues in the indexes both clustered & nonclustered, this solution is the best utility.

Also, not just the indexes, but this tool can fix various SQL database objects & can even retrieve deleted ones. Download the tool & then follow the five quick steps to learn how to rebuild nonclustered index SQl server without errors.

Step-1. Launch the Automated Software on the computer.

Step-2. Simply, Hit the Open button to Add SQL MDF files.

Step-3. Choose Scan Mode first & then Versions of SQL.

Step-4. Now, Enter the Destination Folder path for files.

Step-5. Hit Export to fix Index corruption in SQL Server.

Features of the Tool:-

• Fixes damaged SQL objects like tables, views, triggers, etc.
• Fixes SQL Server functions, stored procedures, rules, etc.
• Repair & recover both nonclustered & clustered indexes.
• Quick & Advanced Scanning mode based on corruption.
• Easily rebuild nonclustered index SQL Server Database.
• Executes the restoration tasks for all the SQL versions.
• Export the database with schema & schema with data.
• Export healthy database to:
  • Live SQL Server
  • CSV File Format
  • SQL Script File
• It Detects SQL Server versions 2022, 19, 17, 16, etc.
• Also, supports both Windows OS & Windows Server.

Also Read: Learn the Difference Between Triggers & Stored Procedures in SQL Server

In A Nutshell

Now, we are aware of the entire procedure to fix Index corruption in SQL Server & rebuild the nonclustered indexes. It’s up to users which method is suitable as per their needs. Both work pretty well but users need to select one based on their urgency & feature requirements. Paying attention to the Nolock hint can help users identify the issue early & act accordingly.