SQL Server monitoring disk IO error, MSDB.DBO.SUSPECT_PAGES_MSSQL

The suspect_pages table is in the msdb database and is introduced in SQL Server 2005. Suspect_pages for maintaining information about suspicious pages

The database administrator is responsible for managing the tables (mainly by deleting the old rows). The suspect_pages table has a size limit, and if the table is full, a new error is not logged. To prevent this table from filling up, the database administrator or system administrator must manually purge the old entries in this table by deleting the rows. Therefore, we recommend that you periodically delete or archive rows that have been restored or repaired or that have old last_update values event_type.

To monitor operations on the suspect_pages table, you can use the Database suspect Data Page event class. Sometimes rows are added to the suspect_pages table for transient errors. If you are adding many rows to the table, there may be a problem with the I/O subsystem. If you notice a sudden increase in the number of rows being added to the table, we recommend that you check to see if there is a problem with the I/O subsystem.

The following table shows the errors that are recorded in the Event_type column of the suspect_pages table.

Error Description	event_type value
823 error caused by operating system CRC error, or 824 errors other than checksum error or page RIP (for example, page ID error)	1
Bad checksum	2
Torn page	3
Restored (page has been restored after being marked as an error)	4
Repaired (DBCC repaired page)	5
was released by DBCC	7

Temporary errors are also recorded in the Suspect_pages table. The source of the transient error includes an I/O error, such as a cable disconnect, or a page that has not passed a duplicate checksum test temporarily.

How the database engine updates the suspect_pages table

The database engine performs the following actions on the suspect_pages table:

If the table is not full, each time a 824 error occurs, the table is updated to indicate an error occurred and the error counter is incremented accordingly.
If a page that is repaired by a repair, restore, or release operation still has an error, its number_of_errors count is incremented and its last_update column is updated
After a listed page is repaired by a restore or repair operation, the action updates the suspect_pages row to indicate that the page has been repaired (Event_type = 5) or restored (Event_type = 4).

If you run a DBCC check, the check marks all the error-free pages as fixed (event_type = 5) or released (Event_type = 7).

Automatically update suspect_pages table

Attempt to read a page in a data file after one of the following fails, the database mirroring partner updates the suspect_pages table.

823 error caused by the operating system CRC error.

824 error (logical corruption such as page tearing).

The following actions automatically delete rows from the suspect_pages table.

ALTER DATABASE REMOVE FILE
DROP DATABASE
The DBCC CHECKDB repair_allow_data_loss updates the suspect_pages table to indicate which pages have been released or repaired.

RESTORE can also update the list. A full restore, file restore, or page restore marks the page item as restored.