Logical Consistency-Based I/O Error in SQL Server (Errors 823 and 824)

These are severity 24. SQL Server does not hand those out lightly: it is telling you that a read it depended on did not come back correctly, and that it cannot vouch for the integrity of what is on disk. The instinct is to make the error go away. That instinct is what turns a recoverable incident into a restore from a backup nobody checked.

This page is about the moment the error arrives. What comes before it, and what comes after, are separate jobs:

823 and 824 Are Not the Same Failure

They are printed in the same red and they are both severity 24, so they get treated as one thing. They are not, and the distinction is the most useful information on this page.

Put plainly: 823 is the storage admitting it failed. 824 is the storage failing without admitting it. Both escalate to the storage or hardware team, but 824 also means you cannot trust “the SAN reports no errors” as reassurance, because that is exactly the condition 824 describes.

Microsoft’s reference covers error 823 and error 824 in full, and 2 details from those pages belong in the incident. For a read, SQL Server retries the request 4 times before it raises 823, and a retry that eventually succeeds is logged as 825 instead. And when a query hits either error, the message goes back to the application and the connection is terminated, so the first report you get is often a dropped connection rather than the error text.

Two more you may see in the same window, worth recognising rather than chasing separately:

Overwriting a database’s transaction log gave a torn page, where the page signature did not match at all:

Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: torn page
(expected signature: 0xaaaaaaaa; actual signature: 0x8834a602). It occurred during a
read of page (2:0) in database ID 1 at offset 0000000000000000
in file '/var/opt/mssql/data/mastlog.ldf'.

Scribbling over pages inside the data file, leaving the header valid, gave an incorrect checksum instead, and only after recovery had already rolled forward 16 transactions:

16 transactions rolled forward in database 'master' (1:0).
Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0x524b60b1; actual: 0x8af60429). It occurred during a read of page (1:56)
in database ID 1 at offset 0x00000000070000
in file '/var/opt/mssql/data/master.mdf'.

Same error number, same severity, two different reasons quoted in the message. That quoted reason, torn page or incorrect checksum, is worth reading before anything else: a torn page means a write was interrupted partway, a bad checksum means the bytes changed after they were written. Both of these were on database ID 1, which is master, and that is an instance that will not start rather than a database you can restore at leisure.

The First Five Minutes

In order, and the order matters. Every step here is read-only.

1. Confirm you have a restorable backup, and stop it being overwritten. Before diagnosis, before anything. Backup retention windows have ended more recoveries than corruption has.
2. Read the message properly. The file path and the offset tell you which database and which volume. The page ID in an 824 tells you how localised this is.
3. Check suspect_pages. It answers “is this the first time” in one query, below.
4. Search the error log for the neighbours. An 823 rarely arrives alone, and an 825 earlier in the week changes the story from “sudden” to “ignored”.
5. Then, and only then, run DBCC CHECKDB to find out the actual extent. Without repair options.

-- 1. Has SQL Server already recorded bad pages on this instance?
--    Empty is the healthy answer. Any row is a page SQL Server could not read cleanly.
SELECT DB_NAME(database_id) AS database_name,
       file_id, page_id, event_type,
       -- 1 = 823/824 error   2 = bad checksum   3 = torn page
       -- 4 = restored        5 = repaired       7 = deallocated
       error_count, last_update_date
FROM msdb.dbo.suspect_pages
ORDER BY last_update_date DESC;

-- 2. What else did the error log say around the same time?
EXEC xp_readerrorlog 0, 1, N'823';
EXEC xp_readerrorlog 0, 1, N'824';
EXEC xp_readerrorlog 0, 1, N'825';   -- the warning that came first

How Long Has This Database Been Unchecked?

The question that decides how much of your backup chain is trustworthy is not “is it corrupt now”, it is “when did I last know it was clean”. Everything after that point may contain the problem, including your backups.

SELECT d.name,
       CONVERT(varchar(19), DATABASEPROPERTYEX(d.name,'LastGoodCheckDbTime'), 120) AS last_known_good,
       DATEDIFF(day, CAST(DATABASEPROPERTYEX(d.name,'LastGoodCheckDbTime') AS datetime), GETDATE()) AS days_ago,
       d.page_verify_option_desc AS page_verify
FROM sys.databases d
WHERE d.state_desc = 'ONLINE' AND d.database_id > 4
ORDER BY days_ago DESC;

Real output from running that, and it contains the trap:

That first row does not mean the check is 126 years old. It means CHECKDB has never successfully completed on that database. LastGoodCheckDbTime returns 1900-01-01 when there is no value, so any “days since” calculation turns “never” into an enormous number that looks like a formatting bug and gets skipped over. If you build a report on this property, test for the 1900 date explicitly rather than trusting the arithmetic.

It is also worth knowing what a clean check looks like, because it looks like nothing:

Drop NO_INFOMSGS and it does say so out loud. The same database, same run:

DBCC results for 'DemoDatabase'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'DemoDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

That is the line to capture and keep, because “0 allocation errors and 0 consistency errors” plus a timestamp is the evidence that your last known good point is real. It is also what the LastGoodCheckDbTime property above is recording for you, which is why that property is worth more than a memory of having run it.

Nothing Tells You Unless You Set It Up

SQL Server ships with no alerts configured. Not fewer than you need, none. I checked msdb.dbo.sysalerts on a working instance while writing this and it held zero alerts for 823, 824 or 825, which is the out-of-the-box state on every install rather than an oversight on that one.

So the sequence on a default install is: 825 arrives at severity 10 and nothing happens, then 823 or 824 arrives at severity 24 and you find out from a user. Severity 24 does at least reach the error log and the Windows event log, both confirmed as is_event_logged = 1 in sys.messages, so there is a record. There is simply nobody watching it.

Alerting on all 3 is a 10-minute job and it is the difference between finding this in a maintenance window and finding it in an outage. The 825 post covers setting that up, and 825 is the one most people leave out.

What Not To Do

Common Questions

Related Scripts

• Error 825, the read-retry warning, the notice that arrives before this one and that nothing alerts on
• DBCC CHECKDB Found Corruption, the repair-or-restore decision once the extent is known
• Backup & Recovery scripts, coverage and restore readiness, which is what step one of the list above depends on
• Get Suspect Pages and Integrity Checks, step 3 of the list above as a script, msdb.dbo.suspect_pages across the instance
• Get Last DBCC CHECKDB, the last-known-good date for every database, with the 1900-01-01 value treated as never rather than as 126 years
• SQL Server Errors: The Complete Guide, the index for the whole error series