Know How to Monitor SQL Database Suspect Pages
In SQL Server, there are many key things to be monitors such as CPU, Memory, and other I/O. However, one more thing that rarely comes up for monitoring is Suspect Pages in dbo.suspect.pages table in MSDB database. Having a mechanism to observe and alert on the table is used to do as a part of the observation and for SQL Server Health Management. Therefore, this blog would be extremely helpful for you if you are handling SQL Server.
Corruption in SQL Server Database
We all wish to avoid or prevent a database from corruption, but there is some corruption, we cannot avoid or prevent. As a result, we face different corruption while using the SQL server database. We cannot avoid, but we can perform certain tasks that will be helpful to catch the corruption issues.
- By ensuring that the databases are using CHECKSUM page verification
- By doing DBCC CHECKDB on a regular basis.
- By using the CHECKSUM option while performing backups.
- By monitoring the 823, 824, & SQL server 825 error.
- By monitoring the table content of dbo.suspect_pages in MSDB.
There are many other ways to check corruption issues but Monitor SQL database Suspect pages are among one of them. Before proceeding further let us understand about dbo.suspect_pages.
Important Note: Additionally, in order to remove corruption from the database files, I recommend using a third-party tool such as Recover SQL Database Tool. This software has the tendency to remove the corruption from the database files in a few clicks.
What is dbo.suspect_pages
The dbo.suspect_pages is a table within the MSDB database, where the SQL Server logs about corrupt database pages encounter during querying of the database. Therefore, if you have a DML operation which acceses the corrupt Page then it is recorded or logged here. that means you can identify corruption in your database without the DBCC CHECKDB routine.
Monitor Corruption in Database Table
There are several status values based on the type of detected corruption and also tell whether it has been removed or not. So, before monitoring the table it is important to understand the status values. Moreover, a simple check is not sufficient for more than one returned record from the table.
These foremost statuses are indicating that there is corruption present in the table and has not been fixed and as such require a solution. The first four values show that corruption is still there and has not been removed yet. For this, it is necessary to identify the corruption in the pages and the database This can be done by joining sys.databases and sys.master_files as shown below:
As a result, this query will provide you high-level view where the user found corruption in the SQL Database. The users can use DBCC CHECKDB Commands or backups to recover your crucial database.
Monitoring of SQL Database Suspect Pages
While monitoring the dbo.suspect_pages table, we have to identify the number of suspect pages that have been logged or encountered in the database. When the value of suspect pages found to be greater than 0, then some form of report is required. In this situation, my preference is to use a scheduled job to query the suspect pages table and inform from there using database mail.
Manage Suspect Table Using SQL Server Management Studio
- Go to Object Explorer, connect to the Database engine>>Expand the instance, and then expand Databases.
- In System database>>expand MSDB database>>expand Tables, and then expand System Tables.
- Now, expand the dbo.suspect_pages and right-click on the Edit Top 200 Rows.
- In the end, from the query window just edit, delete or update the desired rows.
Monitoring the Fixed Suspect Pages
So far we have discussed how to Monitor SQL Database suspect pages. After monitoring the suspect pages, it is also required to monitor the logs which state that the suspect pages have been fixed in one or another way. Especially in that case When you are using the Database Mirroring in Enterprise Edition or Availability Groups. This is because both these a having MS Automatic Page Repair, that permits suspect pages to recover from its mirror-image of the non-corrupt copy.
So it’s very important to monitor the fixed suspect pages as to know how many suspect pages are fixed.
Conclusion
In this blog, we have discussed How to fix and monitor SQL database suspect pages issue. To overcome or to fix these suspect pages problems use the upper-mentioned methods. These methods can lead you to solve the suspect pages issue. Moreover, for the database corruption issue, it is good to use an automated tool.
