SQL Server – MSDB Suspect pages table and bad pages

 The MSDB Suspect_pages table records suspect pages. Regular monitoring of the MSDB Suspect_pages table is advised ,  particularly for 823 errors ,824 errors,Bad CheckSum and Torn Page.

 How does SQL Server define a page as suspect?  If an 823 or 824 error is experienced

The 823 error (B_HARDERR) is caused when a Windows read\ write failed. The OS issues a cyclic redundancy check (CRC) – an example is a disk error

 The 824 error (B_HARDSSERR)  means the page is read successfully , but there is something wrong with the page.

Examples of  operations where an 823 or 824 error occurs:

1)       Query reading a page

2)       DBCC CHECKDB operation\ DBCC repair operation

3)       Backup\Restore Operation

4)       Drop Database

Troubleshooting notes for msdb.suspect_table

 1)       Some actions automatically clear out the table, such as : ALTER DATABASE REMOVE FILE, DROP DATABASE

2)       Set up a regular process to manage the rows in the msdb..suspect_table. Give priority to restored or repaired (Event Type 5 & 7)

3)       A big clue there may be IO subsystem issues is to monitor the record count in the table. If there are sudden spikes investigate further by talking to the storage guys

4)       The 824 error is an interesting one. It means there is a logical consistency error was detected during a read operation. In most cases I’ve seen the 824 correlates with an IO subsystem problem. Fix immediately. Contact me if you need any help

SELECT DB_NAME(database_id),[file_id],page_id,
CASE event_type 
WHEN 1 THEN '823 or 824 or Torn Page'
WHEN 2 THEN 'Bad Checksum'
WHEN 3 THEN 'Torn Page'
WHEN 4 THEN 'Restored'
WHEN 5 THEN 'Repaired (DBCC)'
WHEN 7 THEN 'Deallocated (DBCC)'
END,
error_count,
last_update_date
FROM msdb..suspect_pages

 Related Posts

 SQL Server – The server is down and when to call for External Help

DBCC CHECKDB

SQL Server - Recover from a Suspect database

SQL Server - sys.dm_os_loaded_modules