Fix Corrupt Indexes in SQL Server with an Apt Solution

Daniel Jones | June 16th, 2017 | SQL, SQL Server 2016 |

Overview

The issue started two days back, when one of my SQL Server was reported the corrupt indexes in SQL Server. We had been lurched records from the table. It seemed like replication from the SQL Server was broken. We consider SQL Server must be at fault many a times in case something goes wrong. We all are seemingly correct. Grabbing the cane, I struggled with my system and hobbled over the system console. I was unable to track what generated the problem the SQL Server warehouse. I made a quick note that many rows were missing. It came to me like a terrifying situation which triggered me to capture the update and delete activity in the source table. The primary key on the database table is RowID and it was missing and I had to figure out what wrong had happened? It may even be because of the corruption in Non-clustered indexes.

Solution to Fix Corrupt Index in SQL Server

We all struggled like anything and came across few consequences:

  • It happened because of Corruption in non-clustered indexes.

So, we all geared up and tried to resolve it via many approaches. The best thing about the error was that it was easier to handle non-clustered corruption in SQL Server, as compared to actual data page. As the indexes are just redundancies of the actual data and it can be rebuilt too. We decided to use the hexadecimal editor scheme to damage non-clustered index, not the data. We came across few possible outcomes:

  • Corrupt may be on index or table level. For resolving this, the IndexID provided with DBCC output. We figured out that the IndexID was at 0 and no indexes were defined for the table.

TIP: An indexed of inculcates a clustered index. A non clustered index comes out with a value of 2-250.

How to Check Corrupt Index in SQL Server?

We performed a necessary repair on NEOID column and made a non-clustered index for one table in case of 1 NEOID Column. For this, page value of index was necessary to be figured out for only one table. Afterwards, plug the page to the non clustered index in DBCC PAGE.

To retrieve the value of index use DBCC command

We need to call it undocumented, DBCC INDID.

Mentioned below is the syntax for it:

DBCC INDID (DBID, TABLEDID,-1)

Finding Newly-Indexed Table

DBCC ind(21, 232306789, -1)

In this case, the IndexIDS are mostly zero with many IndexID values of 2. It indicates that it is a non-clustered index.

Find page of the new non-clustered index

Now run the DBCC PAGE again, for plugging in the page information:

DBCC TRACEON (6304);
GO
DBCC PAGE (NEO,1,170,3)
GO

It results the returned hexadecimal value (HEAP RID) representing every row in index for page interrogation.

Search for non-clustered index from one table with DBCC PAGE

Modify the corrupt database again using HEX Editor or zero out the HEAP RID and it does not corrupt the database in same way as changing the actual page. Another case occurs that while running DBCC CHECKDB (‘neo’) WITH PHYSICAL_ONLY, then the INDEXID of the corrupt object is marked as 2 i.e., non-clustered index.

Afterwards, all requirements is repairing the damage instead of restoring it from backup or running DBCC.

CHECKDB with REPAIR_ALLOW_DATA_LOSS command. Simply, drop and recreate non-clustered indexes.

Final Word

From the above points, we are really well groomed regarding the method to Fix the Corrupt SQL Server. In addition to it, the article serves the users with an appropriate method to resolve the corruption in non-clustered indexes with SQL Database Recovery Tool.

Free Download
100% Secure

Transaction Log Restore