Fixing Index B-Tree Corruption in SQL Server

1. Bad I/O Subsystem (The Most Common Cause)

If you're seeing error 823 or 824 in the SQL Server error log, the storage layer is almost always the culprit. A failing disk, bad cable, or buggy storage driver writes garbage to the index pages, corrupting the B-tree structure. This isn't a SQL Server bug — it's hardware or firmware lying to you.

How to Confirm It

Run DBCC CHECKDB('YourDatabaseName') with no options first. If it reports corruption with page IDs and a checksum failure, you're looking at I/O issues. Check the Windows System Event Log for disk errors — look for event IDs 7, 11, 15, or 50 from the disk driver.

The Fix

1. Stop the application — don't let writes hit the corrupted pages.
2. Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to get the full list of corrupted pages.
3. If the corruption is limited to one index (not whole table or system tables), use ALTER INDEX ... REBUILD to rebuild it. Example:
   

   ALTER INDEX PK_YourIndexName ON dbo.YourTable REBUILD;

4. If the index is nonclustered and the base table is clean, drop and recreate it. Faster than a rebuild sometimes.
5. If CHECKDB shows corruption across multiple objects, switch to DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD). This rebuilds all indexes without data loss. Run it in single-user mode:
   

   ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD);
   ALTER DATABASE YourDatabaseName SET MULTI_USER;

6. After the repair, replace the bad disk or SAN LUN. Don't skip this — the corruption will come back.

I've seen this exact scenario on Dell PowerEdge R740s with bad HBA cables. A year later, same server, same slot — same corruption. Fix the hardware.

2. Page Splits Gone Wrong (The Ugly One)

Not all corruption is from hardware. Sometimes SQL Server itself writes a corrupt page during a split — rare but real. This shows up as error 8966 with a message like "Failure to read and latch page (1:345) with latch type LATCH_SH". The B-tree node's pointers don't match the actual next page.

How to Spot It

You'll see queries hanging or returning wrong results. CHECKDB finds allocation errors or latch timeouts. The corruption is usually isolated to one index or one partition.

The Fix

1. Don't bother with REPAIR_ALLOW_DATA_LOSS here unless you're desperate. It deletes rows. Use it only if backups are months old.
2. Instead, run a targeted rebuild:
   

   ALTER INDEX IX_YourIndexName ON dbo.YourTable REBUILD PARTITION = ALL;

3. If the index is huge (100GB+), use WAIT_AT_LOW_PRIORITY in SQL Server 2014 SP2 and later to avoid blocking:
   

   ALTER INDEX IX_YourIndexName ON dbo.YourTable REBUILD WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));

4. If the corruption is in a columnstore index, drop it and recreate. Rebuild often fails.

Page split corruption can also be triggered by a bug in the storage engine — check your SQL Server build. Anything pre-CU10 on SQL Server 2017 had a known issue with large object (LOB) columns and page splits. Update your instance.

3. Dormant Corruption From Old Backups

This one bites people who think "I have backups, I'm safe." If you restored a corrupt backup, you just imported the B-tree corruption. The error doesn't show up until you query that index. I've walked into shops where the nightly backup job ran for months with WITH CHECKSUM disabled, and every restore was silently corrupt.

How to Detect It

Run RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourDB.bak' WITH CHECKSUM. If it passes, you're probably clean. But VERIFYONLY doesn't check all pages — it only validates the backup file format. You need to restore the backup and run DBCC CHECKDB to be sure.

The Fix

• Stop taking corrupt backups — enable BACKUP WITH CHECKSUM in your maintenance plan. It adds overhead but catches corruption before it spreads.
• If you find corruption in the restored database, you have two options:
  1. Find a clean backup from before the corruption started. Restore that.
  2. Use DBCC CHECKDB('DatabaseName', REPAIR_ALLOW_DATA_LOSS) as a last resort. It deletes corrupt rows. Do this only if you accept data loss.
• After fixing, run DBCC CHECKDB again to confirm clean state.

Real story: A client restored a 2TB production database from backup. Everything ran fine for three days until a query hit a corrupt nonclustered index. Turns out the backup had been taken during a power glitch that caused a torn page. The backup job ran WITH NO_CHECKSUM. Took them 12 hours to restore from a different backup chain.

Quick-Reference Summary Table

Here's a cheat sheet for the three causes and what to do first.