SQL Server – MSDB Suspect pages table and bad pages

10 October,2012 by Jack Vamvas

 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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – MSDB Suspect pages table and bad pages


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer