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_type 5 records pages DBCC repaired (REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS); event_type 7 records pages DBCC deallocated, which is a data-loss outcome of REPAIR_ALLOW_DATA_LOSS.

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 4345 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;

Anyone with access to msdb can read the data in suspect_pages. Modifying rows requires UPDATE permission on the table; members of the db_owner fixed database role in msdb or the sysadmin fixed server role 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.

Related guides

• SQL Server blocking chains: finding the head blocker before workers run out
• SQL Server buffer cache hit ratio low: when the working set no longer fits in memory
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim
• SQL Server Error 701: there is insufficient system memory to run this query
• SQL Server Error 823 and 824: I/O and logical consistency errors
• SQL Server Error 825: read-retry succeeded and the disk is failing
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators