Monday , December 5 2022

SQL Server Suspect Pages

In today’s article, we will talk about how to detect SQL Server Suspect Pages.

I will explain how you can detect database corruption using the suspect_pages table in the msdb database.

SQL Server announced in 2005 that the SUSPECT_PAGES table is available in the msdb system database. Since SQL Server 2005, every corrupted page information is added to the msdb.dbo.suspect_pages table.

Due to SQL Server database structure, if it encounters one of the following errors, it is considered corrupted. (According to the information on MSDN)

Error 823 occurs when a CRC (Cyclic Redundancy Check) error occurs by the operating system, such as disk failure or some hardware failure. A Windows read or write job has failed.

Torn Page Logical Consistency – 824 error – This error occurs because windows is reading the page successfully but the sql server is experiencing an inconsistency on the page. It has an infrastructure similar to the 823 error.

When will a record arrive in the SUSPECT_PAGES table?

The SQL Server database engine provides logging of page corruptions encountered during any of the following operations.

During the execution of the DBCC CHECKDB command

During Database Backup and Restore

During a corrupted page read of the query being run

During DBCC CHECKDB REPAIR_ALLAW_DATA_LOSS operation

While deleting database – Drop Database operations

Important note! :

We should run the DBCC CHECKDB command periodically during maintenance.

What are the errors recorded in the SUSPECT_PAGES table?

The following table contains the descriptions of the errors according to the event_type data.

Event_type Error Description
1 Error records 823 and 824 at the operating system level – CRC Error
2 Bad CheckSum
3 Torn Page
4 Restored (The page was restored after it was marked bad) –The page was broken, but it has been repaired.
5 Repaired with DBCC
7 Deallocated by DBCC

We can find corrupted pages using the following query.

Maintaining the SUSPECT_PAGES table

Database administrators should periodically review the SUSPECT_PAGES table and delete old rows as this table is limited to 1000 rows.

If the table is full, then new errors will not be recorded.

In order to delete data from the SUSPECT_PAGES Table, you must be logged into SQL Server with a user with db_owner authority or sysadmin authority from the msdb database.

 

About Çağlar Özenç

Leave a Reply

Your email address will not be published. Required fields are marked *