Fix SQL Server Error 823: Index Corruption Guide

If you're seeing error 823 in SQL Server, you're probably panicking. I get it—your database just threw a fit, and you're worried about losing data. But here's the thing: this error usually means one or more indexes got corrupted, not the whole database. You can fix it without restoring from backup most of the time. Let me show you how.

First, Check What's Broken

Open SQL Server Management Studio (SSMS). Connect to your server. Open a new query window. Run this command:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Replace YourDatabaseName with the actual name of your database. After you run this, look at the results. You'll see lines like "CHECKDB found 0 allocation errors and 3 consistency errors." If it says 0 errors, you're lucky—the error 823 might be a one-time glitch. But if you see any number greater than 0, keep going.

Pay attention to the object IDs listed in the errors. That's your corrupted index. Write down those IDs.

Get the Index Name from Object ID

Now run this query to find the actual index name and table:

SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName, index_id
FROM sys.indexes
WHERE object_id = 123456789;  -- replace with your object ID

This gives you the table name and index name. Write those down too.

Rebuild the Corrupted Index

Here's where we fix things. Run this command, but replace the placeholders with what you found:

ALTER INDEX [YourIndexName] ON [dbo].[YourTableName] REBUILD;

For example, if your table is Orders and the index is IX_OrderDate, you'd write:

ALTER INDEX [IX_OrderDate] ON [dbo].[Orders] REBUILD;

After you run this, you should see "Command(s) completed successfully." No errors. That's a good sign.

Now run DBCC CHECKDB again. If it reports 0 errors, you're done. The corruption is fixed.

Why This Works

Index corruption happens when the disk writes data wrong, or when SQL Server crashes mid-write. The index pages get scrambled. Error 823 is SQL Server telling you "I can't read this index page because the checksum doesn't match."

Rebuilding the index forces SQL Server to read the underlying table data (which is usually fine) and write new, clean index pages. It's like demolishing a crooked wall and building a new one from the same bricks. The bricks (your table data) are good, but the wall (the index) was built wrong. Rebuild fixes the wall.

I've seen this fix work on SQL Server 2016, 2019, 2022—all the same. It's my go-to move when someone calls me with error 823.

Less Common Variations

Sometimes the corruption is worse. Here's what to do if the simple rebuild fails.

Error 823 with "I/O error" or "Page read failure"

This means the disk itself might be failing. Run this to check the specific page:

DBCC PAGE('YourDatabaseName', 1, 123456, 3);  -- replace 123456 with the page number from your error

If you see "Page could not be read" or weird characters, your disk has bad sectors. You need to:

Backup the database immediately (even if backup fails, try it).
Run CHKDSK /R on the disk from command prompt (admin mode).
After fixing disk errors, restore from a known good backup.

Do not rebuild indexes on a failing disk. You'll just corrupt the new indexes too.

Corruption in the system catalog indexes

Rare but nasty. Run:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS;

This checks system tables. If it finds errors, you can't fix them with a normal rebuild. You'll need to restore from backup, or if you don't have one, call Microsoft support. I've only seen this twice in 15 years—once from a lightning strike that fried the RAID controller.

Multiple corrupted indexes across different tables

If DBCC CHECKDB shows many errors, run this script to rebuild all indexes in the database:

DECLARE @SchemaName NVARCHAR(128), @TableName NVARCHAR(128), @IndexName NVARCHAR(128)

DECLARE cur CURSOR FOR
SELECT s.name, t.name, i.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.name IS NOT NULL

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX) = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD;'
    EXEC sp_executesql @sql
    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName
END

CLOSE cur
DEALLOCATE cur

This rebuilds every index in the database. It can take a long time if you have a big database. Run it during low-traffic hours.

Prevention

Index corruption almost always comes from hardware problems or sudden power loss. Here's how to stop it from happening again.

Enable Page Checksums

This is on by default in modern SQL Server, but check anyway. Run:

SELECT name, page_verify_option_desc FROM sys.databases WHERE name = 'YourDatabaseName';

If it says NONE, turn it on:

ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;

This makes SQL Server write a checksum on every page write. When reading, it checks the checksum. If it doesn't match, you get error 823—but earlier, and with more info.

Use a UPS (Battery Backup)

I know this sounds obvious, but half the corruption I've fixed was from someone's server getting yanked offline during a thunderstorm. A $200 UPS saves thousands in recovery time.

Regular DBCC CHECKDB

Set up a weekly job to run DBCC CHECKDB with WITH NO_INFOMSGS and log the output. If it fails, you catch corruption before it spreads. I schedule mine for Sunday at 2 AM.

-- Example job step
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, PHYSICAL_ONLY;

The PHYSICAL_ONLY option is faster—it only checks the physical structure, not the logical consistency. Run the full check once a month.

Monitor Disk Health

Use SQL Server's sys.dm_io_virtual_file_stats to see if reads are failing. Run this:

SELECT DB_NAME(database_id) AS DB, file_id, io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms DESC;

If you see high stall times or errors, the disk is dying. Replace it early.

Bottom line: Error 823 is scary but often fixable in minutes. Run DBCC CHECKDB, rebuild the bad index, and you're back in business. The real fix is preventing it with UPS and regular checks. Don't let a power flicker ruin your weekend.