SA0087 : Database has suspect pages and needs to be checked

Introduction

Suspect pages in a SQL Server database indicate potential corruption or issues that could affect data integrity and availability.

Description

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

For example:

-- Query to identify suspect pages
SELECT * 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.

How to fix

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:

        SELECT * 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:

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

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix

3 hours per issue.

Additional Information

Manage the suspect_pages Table

Restore Pages

suspect_pages

Analysis Rules