SQL Server database corruption: errors 823, 824, 825 and a DBCC CHECKDB runbook

#The symptom

A query or a backup fails, or the error log shows one of these:

Msg 824, Level 24: SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0x...; actual: 0x...). It occurred during a read of
page (1:12345) in database ID 7 ...

Msg 823, Level 24: The operating system returned error 21(The device is not ready.)
to SQL Server during a read at offset 0x... in file 'D:\Data\Sales.mdf'.

Msg 825: A read of the file 'D:\Data\Sales.mdf' at offset 0x... succeeded after
failing 1 time(s) ...

Caution

Do not restart the instance, detach the database or run REPAIR_ALLOW_DATA_LOSS as a first move. A restart can leave the database in SUSPECT or RECOVERY_PENDING, and repair can delete rows for good. Work through the steps below in order.

#1. Protect what you have

Before you change anything, take a copy of the current state and make sure the backup chain is intact.

-- Tail-of-the-log backup without truncating; works even if data files are damaged
BACKUP LOG [Sales] TO DISK = N'E:\Backup\Sales_tail_emergency.trn'
WITH NO_TRUNCATE, CHECKSUM, CONTINUE_AFTER_ERROR;

-- Confirm the last good full and log backups
SELECT TOP (20) database_name, type, backup_start_date, backup_finish_date,
       has_backup_checksums, is_damaged, physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = N'Sales'
ORDER BY backup_finish_date DESC;

Pause any backup cleanup job so older full backups are not deleted while you work. The last clean backup may be older than you think.

#2. Check storage before the database

Errors 823 and 825 almost always point below SQL Server. Look at the Windows System event log for disk, controller and Ntfs errors, and at the storage or cloud disk health.

Get-WinEvent -LogName System -MaxEvents 200 |
  Where-Object { $_.ProviderName -match 'disk|Ntfs|stor|iScsiPrt' } |
  Select-Object TimeCreated, ProviderName, Id, Message

If the storage is still failing, fixing pages is wasted effort. Move the database (restore to healthy storage or fail over) first.

#3. See which pages are already known bad

SQL Server records bad pages it has hit in msdb:

SELECT DB_NAME(database_id) AS db, file_id, page_id, event_type,
       error_count, last_update_date
FROM msdb.dbo.suspect_pages
ORDER BY last_update_date DESC;

event_type values: 1 = 823 or unspecified 824, 2 = bad checksum, 3 = torn page, 4 = restored, 5 = repaired, 7 = deallocated by DBCC. The table holds at most 1,000 rows; clean out old restored or repaired entries after an incident.

#4. Run DBCC CHECKDB

DBCC CHECKDB (N'Sales') WITH NO_INFOMSGS, ALL_ERRORMSGS;

For a very large database you can start with PHYSICAL_ONLY to get a fast answer, then run the full check later:

DBCC CHECKDB (N'Sales') WITH PHYSICAL_ONLY, NO_INFOMSGS;

Warning

CHECKDB builds an internal database snapshot and reads every page. It uses heavy I/O and tempdb space. On a busy primary, run it on a restored copy instead, or schedule it out of hours. WITH ESTIMATEONLY reports the tempdb space it expects to need.

Save the full output. The last lines tell you the minimum repair level, for example:

CHECKDB found 0 allocation errors and 4 consistency errors in database 'Sales'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Sales).

#5. Work out what is damaged

Each error names an object ID and an index ID. Map them:

USE [Sales];
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id)        AS table_name,
       i.index_id, i.name AS index_name, i.type_desc
FROM sys.indexes AS i
WHERE i.object_id = 1977058079   -- from the CHECKDB message
  AND i.index_id  = 4;

#6. Recover

#6a. Only nonclustered indexes are damaged

The data in the base table is intact. Rebuilding the index from the base table fixes it. A plain ALTER INDEX ... REBUILD may read the damaged index, so drop and recreate:

-- Script the definition first (SSMS > Script Index as > CREATE)
DROP INDEX [IX_Orders_CustomerId] ON dbo.Orders;
CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId] ON dbo.Orders (CustomerId) INCLUDE (OrderDate);

Then run CHECKDB again.

#6b. A few data pages are damaged: page restore

Page restore needs the FULL or BULK_LOGGED recovery model and an unbroken log chain. Enterprise edition can do it online; other editions take the database offline for the restore.

USE master;
RESTORE DATABASE [Sales] PAGE = '1:12345, 1:12346'
  FROM DISK = N'E:\Backup\Sales_full.bak' WITH NORECOVERY;

RESTORE LOG [Sales] FROM DISK = N'E:\Backup\Sales_log_0100.trn' WITH NORECOVERY;
RESTORE LOG [Sales] FROM DISK = N'E:\Backup\Sales_log_0115.trn' WITH NORECOVERY;

-- Bring the pages up to date with a fresh log backup, then restore it
BACKUP LOG [Sales] TO DISK = N'E:\Backup\Sales_log_pagefix.trn';
RESTORE LOG [Sales] FROM DISK = N'E:\Backup\Sales_log_pagefix.trn' WITH RECOVERY;

#6c. Damage is widespread or in system tables

Restore the whole database from the last clean full backup plus logs, side by side under a new name, check it, then swap. The point-in-time restore runbook covers the commands.

#6d. No usable backup: repair as a last resort

ALTER DATABASE [Sales] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (N'Sales', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
ALTER DATABASE [Sales] SET MULTI_USER;

Caution

REPAIR_ALLOW_DATA_LOSS fixes structure by deallocating damaged pages. The rows on them are gone and foreign keys may now point at nothing. Get written sign-off from the data owner, take a copy of the files first, and run DBCC CHECKCONSTRAINTS afterwards to find orphaned rows.

#Availability groups and managed services

• Always On AG: secondaries repair a bad page automatically from another replica. Check what has been fixed with SELECT * FROM sys.dm_hadr_auto_page_repair;. Repeated entries mean failing storage on that replica.
• Amazon RDS for SQL Server: you can run DBCC CHECKDB, but you cannot do a page restore. Restore a snapshot or point-in-time copy to a new instance and move the data.
• Azure SQL Managed Instance: Microsoft runs integrity checks on the platform. Open a support case and use point-in-time restore to a new database if you need data back.

#Prevent it next time

1. Make sure every database uses checksums:

   SELECT name, page_verify_option_desc FROM sys.databases WHERE page_verify_option_desc <> 'CHECKSUM';
   ALTER DATABASE [Sales] SET PAGE_VERIFY CHECKSUM;
   

   Existing pages get a checksum only when they are next written.

2. Back up WITH CHECKSUM and test restores, not just RESTORE VERIFYONLY.

3. Run DBCC CHECKDB on a schedule, at least weekly. For very large databases, offload the full check to a restored copy and run PHYSICAL_ONLY on production.

4. Alert on 823, 824 and 825 so you hear about them from SQL Agent, not from a user:

   EXEC msdb.dbo.sp_add_alert @name = N'Error 824 - logical I/O', @message_id = 824,
        @severity = 0, @include_event_description_in = 1;
   EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 824 - logical I/O',
        @operator_name = N'DBA Team', @notification_method = 1;
   -- Repeat for 823 and 825, plus severities 23-25
   

5. Keep backup retention longer than the gap between integrity checks. If CHECKDB runs weekly but you keep only three days of backups, you can find corruption with no clean backup left to restore.