How to fix a corrupt index in SQL Server

Note: This article mainly concerns non-clustered indexes that have been corrupted e.g. broken as a result of a failing disk or disk subsystem. This failure might not be identical to other failures!
IMPORTANT: This is not advice on how to save a corrupt database! Always ensure you have functional recent backups, a complete backup chain and a proven recovery routine! Never use the option REPAIR_ALLOW_DATA_LOSS unless you are willing to loose data and database integrity!

In the good old days of SQL Server 2000 you could rebuild indexes when they had become corrupt, using ALTER INDEX REBUILD or by issuing a DBCC CHECKDB / CHECKTABLE with REPAIR_REBUILD.

As of SQL Server 2008 and later it appears that corrupt (non-clustered) indexes (under some conditions) no longer can be rebuilt in this fashion, at least not using ALTER INDEX REBUILD or for instance DBCC CHECKTABLE (¨TableName, REPAIR REBUILD). If you analyse the query plans you might see that the corrupt index is referenced by the ALTER INDEX REBUILD or even by the CREATE INDEX, this would be a logical explanation, but stops you from fixing the corrupt index!

In SQL Server 2000 I used to make a new identical index first, since with big indexes on big tables this could take time, potentially blocking running queries (Enterprise Editon has an online index rebuild option, but seems like this also no longer will fix a broken index), then dropping the corrupt index and renaming the new replacement index. This is not really a rebuild, since you make a new index. Turns out creating a new identical index also will fail with a rather nasty error about how the page is corrupt and needs to be restored.

In the end I had to drop the corrupt index and the create it again, this solved the problem, but it is not an online operation in any editions of SQL Server. Another option would be a page restore, providing you have the backups required!

Finally, SELECT * FROM msdb.dbo.suspect_pages might give you more insight as to what is corrupted and the extend of curruption, but this is not always the complete picture! Also important to run DBCC CHECKDB as often as possible to detect any errors as early as possible. Also make sure your hardware and software is monitored and up to date! Backups must be in order... and available for as long as required! Down time also plays a role, as does HA solutions, including AGs, clustering and mirroring.