SA0087 : Database has suspect pages and needs to be checked

Identifying these pages is crucial to maintaining a healthy database environment.

For example:

1-- Query to identify suspect pages
2SELECT * FROM msdb..suspect_pages;

This query checks the msdb..suspect_pages system table to find pages that might be corrupt or unavailable, flagging them as ‘suspect’. These could be due to hardware failures, software bugs, or improper shutdowns.

• Corrupted pages can lead to data loss or unavailability of specific database objects.

• Performance Degradation: Repeated access to suspect pages can slow down queries significantly.

Ensure the integrity and availability of your database by identifying and addressing suspect pages in SQL Server.

Follow these steps to address issues identified as suspect pages:

1. Run the command DBCC CHECKDB WITH NO_INFOMSGS to perform a comprehensive check of your database, which will reveal any corruption or suspect page issues.

2. Review the results of the DBCC CHECKDB command to identify specific errors or warnings indicating suspect pages.

3. Retrieve information about suspect pages by executing the following query:

   SQL

   Copy

   1SELECT * FROM msdb..suspect_pages;

4. Analyze the output to determine the cause and scope of the corruption, considering possible triggers such as hardware failures or improper shutdowns.

5. Use the information gathered to take corrective action, such as restoring from a backup or repairing the database using DBCC CHECKDB with the repair option, if necessary.

For example:

1-- Example query to identify suspect pages
2SELECT * FROM msdb..suspect_pages;

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Maintenance Rules, Bugs

Manage the suspect_pages Table

Restore Pages

suspect_pages

Other Resources