Index B-Tree Corruption Detected – Real Fix That Works

You've got that ugly error and your database queries are failing. I've been there too many times. Last month a client's entire order system went down because of this – they were about to lose thousands of dollars. Let's fix it fast.

First, Stop the Panic – The Fix

This error means a part of your index's B-tree structure is physically damaged. It happens when a disk write fails mid-step or when a power outage hits during index maintenance. The fix is straightforward: rebuild the corrupted index.

But here's the trick – you can't just run a normal rebuild if the corruption is severe. The rebuild might fail because the index can't read its own data. So we do it step by step.

Step 1: Find Which Index Is Corrupt

Run this query in SQL Server to list all indexes with corruption:

SELECT DB_NAME() AS database_name, 
       OBJECT_NAME(object_id) AS table_name, 
       index_id, 
       index_type_desc, 
       avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 30
ORDER BY avg_fragmentation_in_percent DESC;

For Oracle, the error code ORA-00600 [kddlb_bcb_check] points directly to the index name in the trace file. Check the alert.log for the exact object ID.

For MySQL, use CHECK TABLE your_table to see if the index is corrupt. It will say 'Table is marked as crashed'.

Step 2: Rebuild with a Fresh Copy

The safest way is to drop and recreate the index from scratch. Don't trust the ONLINE rebuild option here – I've seen it fail on corrupted B-trees.

First, check if the table has a primary key or unique constraint that depends on this index. If yes, you'll need to recreate the constraint too. Here's the SQL Server version:

-- Drop the index
DROP INDEX [index_name] ON [schema].[table_name];

-- Recreate it
CREATE NONCLUSTERED INDEX [index_name] 
ON [schema].[table_name] ([column1], [column2]);

For Oracle, use:

ALTER INDEX index_name REBUILD;

But if that fails, you might need:

DROP INDEX index_name;
CREATE INDEX index_name ON table_name (column1, column2);

Step 3: Verify the Fix

After rebuilding, run DBCC CHECKTABLE (SQL Server) or ANALYZE INDEX (Oracle) to confirm corruption is gone. For MySQL, CHECK TABLE your_table should report 'OK'.

If the rebuild itself fails with corruption errors, you have physical disk damage. That's rare but happens. I had a client whose RAID controller was failing silently for months. Replace that disk before doing anything else.

Why This Works

An index B-tree is a sorted structure of pages. When one page gets corrupted – a bad byte in a key value, a broken pointer to the next page – the entire index becomes unreliable. By dropping and recreating the index, you force the database to read all data from the base table and build a fresh B-tree from scratch. That bypasses the corrupted pages entirely.

Rebuilding in place (like ALTER INDEX REBUILD) sometimes tries to use the existing index structure, which can fail if the corruption is in the root page. Drop-and-create is safer because it doesn't touch the old index at all.

Less Common Variations of the Same Issue

PostgreSQL B-tree Corruption

PostgreSQL users see this as ERROR: index "index_name" contains corrupted index entry. The fix is REINDEX INDEX index_name or REINDEX TABLE table_name if the whole table is suspect. I've run into this on PostgreSQL 12 after a server crash. The reindex command worked fine.

MySQL InnoDB Corruption

InnoDB tables often show this error as 'Table 'table_name' is marked as crashed and should be repaired'. The fix is REPAIR TABLE table_name. But if that fails, you can use mysqlcheck --repair --all-databases from the command line. I've also had luck with ALTER TABLE table_name ENGINE=InnoDB to force a rebuild of the table and its indexes.

Corruption in System Databases

If the corruption is in a system database like master or msdb (SQL Server), you can't just drop indexes. You need to restore from a backup or run DBCC CHECKDB ('master') WITH REPAIR_ALLOW_DATA_LOSS as a last resort. I had a client whose tempdb crashed because of B-tree corruption – we just restarted the SQL Server service to recreate tempdb from scratch.

How to Prevent Index Corruption

Prevention is boring but saves you from 3 AM panics.

• Use UPS power – sudden power loss during index writes corrupts B-trees. Every server in a data center should be on a UPS. My client's issue last month was caused by a janitor unplugging the server to vacuum.
• Run regular integrity checks – Schedule DBCC CHECKDB weekly. Catch corruption early before it spreads. For SQL Server, I use Ola Hallengren's maintenance scripts – they're free and solid.
• Monitor disk health – Use SMART monitoring tools. Replace drives when they show reallocated sectors. A bad sector in the middle of an index file is corruption waiting to happen.
• Avoid manual index modifications – Don't use ALTER INDEX ... SET with weird options unless you know what you're doing. I once saw a DBA mess up fill factor and cause corruption after a rebuild.
• Backup before any index rebuild – Always take a full backup before dropping or rebuilding indexes. If something goes wrong, you have a fallback.

That's it. The fix is simple once you stop chasing rabbit holes. Drop the corrupt index, recreate it, and move on. If you hit a system database issue or physical disk failure, get professional help – restoring from backup is safer than a repair that loses data.