Home/Error Library/sqlserver-823-824-torn-page-io-corruption-error

SQL Server

SQL Server 2017 - 2022Storage & Corruption

20 min read

Msg 823 / 824, Severity: 24: I/O error 23 (Data error cyclic redundancy check) / torn page detected

SQL Server Disk Subsystem Corruption: msdb.suspect_pages, DBCC CHECKDB Diagnostics & Page Restore Runbook

Diagnosing hardware/SAN subsystem write failures and checksum mismatches, inspecting msdb.suspect_pages, DBCC CHECKDB recovery and PAGE_VERIFY CHECKSUM policy.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: SQL Server 2017 / 2019 / 2022

Operational Verification & Reliability Metadata

Last Technical Review: 2 Kasım 2026

Tested VersionsSQL Server 2019 CU27, 2022 CU14

Required Privilegessysadmin

Risk Assessment

CriticalRunning REPAIR_ALLOW_DATA_LOSS deallocates corrupted pages causing irreversible data loss; always prefer Page Restore from backup

Expected OutputDBCC CHECKDB reporting 0 allocation errors and 0 consistency errors

Rollback Procedure:Page Restore or Point-in-time restore from latest verified full and log backups

Microsoft Docs: MSSQLSERVER 823 & 824 Errors

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

SQL Server reports critical Severity 24 consistency errors:

plaintext

Msg 823, Level 24, State 2, Line 1
I/O error 23(Data error (cyclic redundancy check).) detected during read at offset in file 'D:\Data\Prod.mdf'.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected checksum: 0x8a1b2c3d; found 0x00000000).

▸What You See in Production

Specific 8KB data pages become unreadable. Affected tables fail queries immediately. Entries populate msdb.dbo.suspect_pages.

▸Root Cause Matrix

Probability	Root Cause	Description
%70	SAN Subsystem / Controller Failure	Write caching failure or torn pages where only partial sector flushes occurred.
%20	Firmware / Driver Faults	Disk controller reports write completion without committing sectors.
%10	Missing PAGE_VERIFY CHECKSUM	Legacy database missing proactive hardware validation.

▸EMERGENCY RUNBOOK (0-5 min)

sql

-- 1. Query suspect pages catalog
SELECT database_id, file_id, page_id, event_type FROM msdb.dbo.suspect_pages;

-- 2. Execute consistency check
DBCC CHECKDB ('Prod') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 3. Perform surgical Page Restore from verified backup
-- RESTORE DATABASE Prod PAGE = '1:2450' FROM DISK = 'D:\Backups\Prod_Full.bak' WITH NORECOVERY;
-- RESTORE LOG Prod FROM DISK = 'D:\Backups\Prod_Log.trn' WITH RECOVERY;

▸PERMANENT BLUEPRINT

1Enforce Checksums: ALTER DATABASE Prod SET PAGE_VERIFY CHECKSUM;
2Weekly Integrity Audits: Schedule regular DBCC CHECKDB maintenance jobs.
3Backup Checksums: Take backups WITH CHECKSUM.

▸Verification

sql

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

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (82)

SQLSERVER5 min