Find database corruption with msdb.dbo.suspect_pages

An underused system table is the msdb.suspect_pages . When SQL Server reads a page and corruption is detected , the page details are logged in  msdb.dbo.suspect_pages

The page id is logged in the following situations:

1) 823 error – certain disk and hardware error codes

2) 824 error – torn pages

3) Bad checksum

4) DBCC deallocation

5) DBCC repaired the page

6) Database Restored after marked as bad

Read SQL Server – MSDB Suspect pages table and bad pages  for more detail on the 823 and 824 errors

What to do with entries in the msdb.suspect_pages ?

I follow these steps if I find a new entry

DBCC CHECKDB () WITH NO_INFOMSGS, ALL_ERRORMSGS

Then analyse what to do.  Top priority to check DBCC CHECKDB results first and then decide on how to proceed

Do not detach the database, restart the server, or apply any exotic database repair techniques , before having a firm plan in place

Review backup strategy for the database and confirm it satisfies Recovery Point objectives (RPO).

If there is a Backup  - then restore from a clean backup - i.e a backup taken prior to the corruption.

If there is no valid Backup and the database is unusable, then things get more complicated.

Some other options are:

a) Script all objects from the corrupted database. This includes all objects - security, indexes, foreign keys . Basically, attempt to extract as much as possible.

If you were able to get enough out , then you may attempt to rebuild the database. This can be a lengthy process.

If you manage to get the database back online - your first task is to implement a backup strategy.SQL Server - Top 10 DBA mistakes - SQL Server DBA

Then initiate an investigation into drivers, firmware and any other factors that may contribute the IO sub system.

b) Use third party recovery software.

Monitoring the msdb.dbo.suspect pages

1)      Run regular script with query

     -- Select  824, bad checksum
SELECT * FROM msdb..suspect_pages    WHERE (event_type = 1 OR event_type = 2 );

2)      Monitor the Database Suspect Data Page event class. This class is triggered when the page details are added to the msdb.dbo.suspect_pages table

Related Posts on SQL Server management

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

7 Essential Steps to Recover a Corrupt SQL Server Database