How to Monitor SQL Database Suspect Pages?

Summary: This blog advises on how to monitor SQL Server Suspect Pages in the dbo.suspect_pages table. It also describes solutions to manage suspect_pages table issue. If you fail to fix a suspect database, try using Stellar Repair for MS SQL software to repair your database and restore it to its normal state.

In my previous blog, I discussed SQL database suspect mode issue. This blog is all about monitoring SQL Server Suspect Pages in the dbo.suspect_pages table located in the MSDB database. Having a mechanism to monitor and alert on this table is something that every DBA ought to do as a part of the Monitoring and Server Health Management task. Therefore, this blog would be highly beneficial for you if you are an IT administrator handling SQL Server in an organization.

Corruption in SQL Server Database

A working SQL Server database is always desired. Nevertheless, it is not always possible to prevent corruption and keep it in a usable condition. Thus, being a Database Administrator you would have to address corruption in the database of SQL Server quickly to have the least impact on its MDF and NDF files and the data objects stored within it. There exist many tasks that you can do to check corruption, and one of them is monitoring SQL server suspect page. However, before that, it is necessary for you to have a complete knowledge of dbo.suspect_pages.

All About dbo.suspect_pages

dbo.suspect_pages is a table that is present in the MSDB database where the Microsoft SQL Server records information of the corrupt database pages (restricted to 1000 rows) that it encounters while DBCC CHECKDB command is executed and also during the normal querying of the database. Consequently, if you have a DML operation that accesses a corrupt page, it is recorded here. This means that you also have the privilege to identify corruption in your database outside of the inbuilt DBCC CHECKDB command provided in SQL Server.

Corruption Monitoring

When you monitor dbo.suspect_pages table, it is vital for you to understand that there are many different status values set based on the ?type of corruption? detected and whether ?corruption has been removed? or not. As such, a simple check for multiple records that is returned from the table is not sufficient.

Status ID/event_type value	Status Description of Error
1	823 or 824 Error that was not a bad Checksum or Torn Page
2	Bad Checksum
3	Torn Page
4	Restored (Page was restored after being marked ?bad?)
5	Repaired (page repaired by DBCC)
7	Deallocated by DBCC

From the table displayed above, the first four status values show that corruption is still present and has not been removed yet. This, thereby, requires attention so that corruption is removed as soon as possible. For this, it is essential for you to identify those pages and the database in which they are present. It is easy to do so if you join out to sys.databases and sys.master_files as is seen here:

The result of this query is a high-level view of where you have corruption in the SQL database. Here, it is important to use DBCC CHECKDB command and backups to recover data from it.

Monitoring of Suspect Pages

When the talk is about monitoring the dbo.suspect_pages table, the most important thing is to detect the number of Suspect Pages that have been encountered and logged. If this value is more than 0, then some form of report becomes necessary. In such a situation, you would have to use a scheduled job to query the table and then inform from there through database mail.

How to Manage suspect_pages Table

Using T-SQL

• Connect the database engine
• Click on New Query
• The following example will delete some rows from suspect_pages table:

-- Delete restored, repaired, or deallocated pages. 
DELETE FROM msdb..suspect_pages 
WHERE (event_type = 4 OR event_type = 5 OR event_type = 7); 
GO

• Check this example, it returns the bad pages present in suspect_pages table:

-- Select nonspecific 824, bad checksum, and torn page errors. 
SELECT * FROM msdb..suspect_pages 
WHERE (event_type = 1 OR event_type = 2 OR event_type = 3); 
GO

Using SQL Server Management Studio

1. Go to Object Explorer, connect to an instance of the Database Engine, expand the instance, and then expand Databases.
2. Under System Databases, expand msdb, expand Tables, and then System Tables.
3. Expand the suspect_pages and right-click Edit Top 200 Rows.
4. Now, from the query window, edit, update or delete the rows which you want.

Monitoring of Fixed Pages

In addition to monitoring the Suspect Pages, it is equally important to monitor the records that indicate that Suspect Pages have been fixed in one or the other way, especially if you are using Database Mirroring in Enterprise Edition or Availability Groups. This is because both these features have ?Microsoft Automatic Page Repair? utility that permits the Suspect Page to be repaired by recovering a non-corrupt copy of the page from its mirror-image or replica. As such, monitoring of Fixed Pages records is quite important. This is because without doing so you would not know as to how many Suspect Pages have been fixed.

Conclusion

This about monitoring and fixing SQL database suspect_pages problem. Use these user-friendly methods to fix the SQL database suspect_pages table issue.

Related Post