01 September,2014 by Tom Collins
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
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
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.
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
SQL Server – The server is down and when to call for External Help
SQL Server - Recover from a Suspect database
SQL Server - sys.dm_os_loaded_modules
7 Essential Steps to Recover a Corrupt SQL Server Database
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |