SQL Server suspect_pages: the durable record of storage corruption

The SQL Server error log recycles on every service restart, and by default only six archived logs are retained. When a storage fault produces errors 823, 824, or torn-page detection, the entries that document it may be gone before anyone investigates. msdb.dbo.suspect_pages is where those events also land, and unlike the error log it survives restarts. Treat it as the durable forensic record of page-level I/O corruption on the instance.

This is an operational reference for teams adding suspect_pages to their corruption-monitoring workflow: what the table records, how rows are written, the event_type lifecycle, the 1000-row ceiling, what it misses, and how to operate it alongside DBCC CHECKDB.

What the table is and why it matters

msdb.dbo.suspect_pages is a system table in msdb. Every time SQL Server encounters a page-level I/O error during a normal read, a DBCC operation, or a backup, the engine writes a row recording the database_id, file_id, page_id, the type of event, an error counter, and the timestamp.

Two properties make it more durable than the error log:

  1. The table lives in msdb. Restarting the engine, recycling the error log with sp_cycle_errorlog, or applying a cumulative update does not clear it. A page that errored three months ago is still in the table this morning.
  2. The table records the precise page address. The error log says “error 824 on database X”. suspect_pages says database 7, file 1, page 4827301, bad checksum, four occurrences since the first event.

The tradeoff is that suspect_pages captures only one class of corruption: pages that hit an 823, 824, or torn-page error at the I/O layer. It is not a substitute for DBCC CHECKDB. Many corruption types CHECKDB detects (metadata corruption, allocation errors, structural index corruption) never produce an 823 or 824 and therefore never appear in suspect_pages.

How rows get written

SQL Server writes a row when it cannot validate a page after reading it from disk. The triggers are:

  • An 823 error: the OS returned an error from the read call. Hardware or filesystem level.
  • An 824 error: the OS returned the page, but SQL Server’s internal checks failed. Three subtypes are possible: bad checksum, torn page, or other logical consistency failure.
  • A torn page detected during recovery: only some of the sectors in the 8KB page made it to disk before the write was interrupted.

Pages are added when accessed. A page can be corrupt on disk but never read, in which case it never appears in suspect_pages. This is one reason a clean suspect_pages table is not proof of integrity.

The table is also written during Always On automatic page repair. When a replica encounters a corrupt page and obtains a good copy from its partner, the suspect_pages row is updated to reflect the resolution (see the event_type table below).

event_type values and the page lifecycle

The event_type column records what happened to the page. Three values mean active corruption; the others mean resolution. Investigate the first three, treat the rest as audit history.

event_typeMeaningWhat to do
1823 OS-level read error, or 824 logical consistency error that is not a bad checksum or torn pageInvestigate storage. Run DBCC CHECKDB on the database.
2824 bad checksumSame as above. The page was written with a checksum that no longer matches.
3824 torn pageSame as above. Typically a power event or storage crash during the write.
4Restored. The page was replaced from a backup or, during Always On automatic page repair, by a secondary pulling a good copy from the primary.Audit only. The corruption is resolved.
5Repaired. DBCC repaired the page (or the primary pulled a good copy from a secondary during AG automatic page repair).Audit only. Confirm with CHECKDB that the repair is sound.
7Deallocated by DBCC. DBCC could not repair and removed the page.Audit only, but expect data loss. Identify what was on the page.

A page can move from event_type 1/2/3 to 4, 5, or 7 as it is restored, repaired, or deallocated. The original error entry is not deleted. SQL Server updates the existing row to reflect the latest state.

The diagram below shows the path a single page takes through the table:

flowchart LR
  A[Healthy 8KB page] --> B[823 or 824 on read]
  B --> C[Row added
event_type 1, 2, or 3]
  C --> D{Resolution}
  D --> E[Restore: event_type 4]
  D --> F[DBCC repair: event_type 5]
  D --> G[DBCC deallocate: event_type 7]
  E --> H[Row stays until DBA removes]
  F --> H
  G --> H

error_count and last_update_date

Two columns deserve attention beyond event_type:

  • error_count increments each time SQL Server records a failure for the same page. A value of 1 is consistent with a transient I/O blip. A value greater than 1 on the same page is a hardware escalation signal: multiple failures on the same sector indicate the medium is deteriorating, not that there was a one-off cable disconnect.
  • last_update_date is the timestamp of the most recent event for the page, including resolution events. Any change to a row updates this column.

Use both columns to triage. Sort by error_count DESC to find pages failing repeatedly; sort by last_update_date DESC to find pages that errored recently.

The 1000-row ceiling

suspect_pages has a hard limit of 1000 rows. When the table is full, SQL Server stops writing new entries. New 823, 824, and torn-page events still go to the SQL Server error log, but the durable record stops growing, and SQL Server raises error 5268 (and previously 8910) to flag the overflow.

Two operational consequences follow:

  1. Row count is a first-class signal. A table approaching 1000 rows means either a long history of unaddressed corruption or an active hardware failure generating many bad pages per minute. Either is urgent.
  2. The table must be pruned manually. SQL Server never removes rows on its own, even after they have been investigated or the underlying corruption has been resolved. The only automatic deletions occur when a database file is removed with ALTER DATABASE ... REMOVE FILE or when a database is dropped. Everything else requires a DBA to delete the row.

What suspect_pages does not catch

Three classes of corruption are invisible to suspect_pages:

  1. DBCC-only corruption. Allocation errors, metadata corruption, structural index problems, and many other forms CHECKDB detects do not trigger an 823 or 824. They never reach suspect_pages. A clean suspect_pages table alongside CHECKDB output that reports errors is normal, not contradictory.
  2. Never-accessed corruption. A page can be corrupt on disk but never read since the corruption occurred. If no query, backup, or CHECKDB touches the page, no row is written.
  3. Transient I/O errors that left no actual damage. A cable disconnect or transient checksum failure can write a row that subsequent CHECKDBs confirm is fine. The row is a true record of an event that happened, but it is not proof the page is still corrupt.

This is why suspect_pages pairs with a regular DBCC CHECKDB schedule. Weekly CHECKDB against production databases catches the corruption types that never produce an 823 or 824. suspect_pages catches the I/O-layer corruption that CHECKDB may take a week to discover, and it preserves it across restarts.

Reading the table

The standard query for active corruption:

-- Active corruption rows only
SELECT
    database_id,
    DB_NAME(database_id) AS database_name,
    file_id,
    page_id,
    event_type,
    error_count,
    last_update_date
FROM msdb.dbo.suspect_pages
WHERE event_type IN (1, 2, 3)
ORDER BY last_update_date DESC;

For full forensic context, including resolved rows that may still need investigation:

-- Full table with lifecycle interpretation
SELECT
    database_id,
    DB_NAME(database_id) AS database_name,
    file_id,
    page_id,
    event_type,
    CASE event_type
        WHEN 1 THEN '823 or 824 (other)'
        WHEN 2 THEN '824 bad checksum'
        WHEN 3 THEN '824 torn page'
        WHEN 4 THEN 'restored'
        WHEN 5 THEN 'repaired'
        WHEN 7 THEN 'deallocated by DBCC'
    END AS event_type_desc,
    error_count,
    last_update_date
FROM msdb.dbo.suspect_pages
ORDER BY database_id, file_id, page_id;

Row count and table fill ratio:

-- How close is the table to the 1000-row ceiling?
SELECT
    COUNT(*) AS row_count,
    1000 - COUNT(*) AS slots_remaining
FROM msdb.dbo.suspect_pages;

Read access to suspect_pages is governed by SELECT permission in msdb. Modifying rows requires UPDATE permission on the table; members of db_owner in msdb or sysadmin can insert, update, or delete.

Operational workflow

A mature corruption-monitoring routine has three parts: alert on new entries, investigate them, and prune the table.

Alert on any new row with event_type 1, 2, or 3. SQL Server has no built-in alert for this. The standard implementation is a SQL Server Agent job that polls the table at a short interval (every 1 to 5 minutes), persists the maximum (database_id, file_id, page_id) or last_update_date it has seen, and fires when a new row appears.

Investigate every new entry. A new event_type 1, 2, or 3 row means a page failed an I/O check. Pair the row with the SQL Server error log around last_update_date to find the matching 823 or 824 entry, which carries the OS error code and the operation that triggered the read. Run DBCC CHECKDB on the affected database. If the database is in an Availability Group, check whether automatic page repair has updated the row to event_type 4 or 5.

Prune investigated rows. Once an entry is understood and either resolved (event_type 4, 5, or 7) or confirmed as a transient blip, delete it from the table. This keeps the row count well below 1000 and ensures the next new-entry alert is meaningful. There is no built-in retention policy.

Warning: Only delete rows you have investigated and documented. Deleting a row does not repair the page on disk; it only removes the durable record. Always pair pruning with an incident record so the page_id remains traceable.

Pruning is a targeted DELETE, typically scoped to specific resolved rows:

-- Delete only rows you have investigated and recorded.
-- NEVER run a blanket DELETE without a WHERE clause.
DELETE FROM msdb.dbo.suspect_pages
WHERE database_id = <db_id>
  AND file_id = <file_id>
  AND page_id = <page_id>;

The checklist below summarizes the recurring cycle:

  • New-row alert on event_type 1, 2, or 3. Active storage degradation. Investigate now.
  • Row count approaching 1000. Table is about to silently stop recording. Investigate immediately and prune.
  • error_count greater than 1 on a single page. Same page failing repeatedly. Treat as hardware escalation.
  • Investigated rows with event_type 4, 5, or 7. Audit history. Safe to delete after the incident is closed.
  • Weekly DBCC CHECKDB. Catches corruption types that never hit suspect_pages.

Pairing with errors 823, 824, and 825

suspect_pages rows correlate with three error log signatures. The related guides on error 823/824 and error 825 cover these in detail; the relevant point here is what each one contributes to suspect_pages:

  • Error 823. The OS returned an error from the read. SQL Server writes a suspect_pages row with event_type 1.
  • Error 824. The OS returned the page but SQL Server’s internal validation failed. event_type is 1 (other), 2 (bad checksum), or 3 (torn page) depending on the specific failure.
  • Error 825. A read failed, SQL Server retried, and the retry succeeded. This is the disk-failing canary. The read succeeded, so no suspect_pages row is written because no corruption was ultimately detected. Watch error 825 in the error log, not in suspect_pages.

The asymmetry matters. Error 825 indicates a failing disk that has not yet produced a persistently corrupt page. suspect_pages indicates a corrupt page that has been observed. Both signals are needed; neither is sufficient on its own.

How Netdata helps

suspect_pages is a point-in-time table, not a streaming metric. The value of monitoring it is in correlating new rows with the surrounding storage and engine signals.

  • Row count trended over time. Plotting suspect_pages row count per interval exposes both the steady accumulation that signals an unhealthy table and the sudden jump that signals a hardware event in progress.
  • New-row events correlated with I/O stall. When a new suspect_pages row appears, the same time window in sys.dm_io_virtual_file_stats typically shows latency spikes on the affected file. The two together narrow the failure from “the instance has corruption” to “this volume is producing bad pages”.
  • Correlation with errors 823, 824, and 825. Error log signals land on the same time axis as suspect_pages changes. A burst of 825 retries that precedes a new suspect_pages row is the signature of a disk that was already failing.
  • Database state transitions. A database moving from ONLINE to SUSPECT often coincides with a flood of new suspect_pages entries. Alerting on both together reduces false positives.
  • Per-second granularity. Storage faults can produce many bad pages per minute. Per-second sampling captures the slope that minute-level polling flattens.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and anomaly detection alongside CHECKDB scheduling and error log collection.