How to Monitor SQL Server Suspect Pages and Repair Them

What is the Suspect Pages Table?

SQL Server stores suspect pages in a system table of the msdb database. Microsoft documents the table as one row for every page that failed with a minor 823 error or an 824 error and sets a limit of 1,000 rows.

Six columns describe every row.

Which Event Types Mark Suspect Pages?

Six event types appear in the Microsoft documentation. Types 1 to 3 mark open problems while types 4, 5 and 7 mark pages that SQL Server or an admin handled.

Microsoft lists no event type 6.

What Adds Suspect Pages to the Table?

Five events add or update rows:

• Minor 823 Errors. A disk error that SQL Server reports as an 823 error adds the page with event type 1.
• 824 Errors. A bad checksum adds the page with type 2 and a torn page adds type 3 while a bad page ID adds type 1.
• DBCC Repairs. Microsoft states that DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS updates the table for every page it deallocates or repairs.
• Restores. A full, file or page restore marks the page entries as restored.
• Automatic Page Repair. A database mirroring partner or an Always On availability replica updates the table after a failed attempt to open a page.

What Needs a Check Before You Monitor Suspect Pages?

Check seven facts before the monitoring of suspect pages starts:

• Permissions. Anyone with access to msdb queries the table while members of the db_owner role in msdb or the sysadmin role insert, update and delete rows.
• Row Count. Microsoft states that a full table logs no new errors so count the rows first.
• Page Verify Option. Microsoft lists CHECKSUM page verification among the steps for error 824 so find databases with another setting.
• Database Mail. Methods #2 and #3 need Database Mail with a working profile.
• Operator. Alerts notify an operator so create one with an email address.
• Agent Service. Open SQL Server Configuration Manager and confirm that the SQL Server Agent service has started.
• Backups. Locate a recent full backup plus all log backups taken after it.

Use the two statements below for the second and third facts.

SELECT COUNT(*) AS rows_in_table
FROM msdb.dbo.suspect_pages;

SELECT db.name
FROM sys.databases AS db
WHERE db.page_verify_option_desc IN (N'NONE', N'TORN_PAGE_DETECTION');

5 Methods to Monitor and Repair Suspect Pages

Five methods monitor and repair suspect pages and four of them are free. Methods #1 to #3 watch the table while methods #4 and #5 repair the pages that the table flags.

Match your PC against the software needs before you download. Windows 11, 10, 8 and 7 plus Server 2016, 2012 R2 and 2008 host the installation. Free storage of 100 MB and 4 GB of RAM are needed too. Install .NET Framework 4.5 or a newer version first.

Method #1: Query the Table in SSMS (Free, Start Here)

Microsoft states that anyone with access to msdb queries the table. One statement shows the suspect pages of every database on the instance.

• Open SSMS and connect to the instance.
• Select New Query.
• Paste the statement below to join sys.databases and sys.master_files so the database name and the file path appear beside the page.
• Select Execute and check the event_type column.

SELECT db.name AS database_name,
       mf.physical_name,
       sp.file_id,
       sp.page_id,
       sp.event_type,
       sp.error_count,
       sp.last_update_date
FROM msdb.dbo.suspect_pages AS sp
JOIN sys.databases AS db
  ON db.database_id = sp.database_id
JOIN sys.master_files AS mf
  ON mf.database_id = sp.database_id
 AND mf.file_id = sp.file_id
ORDER BY sp.last_update_date DESC;

Event types 1, 2 and 3 need action while pages with the other three types were handled.

Note. The query shows suspected pages and not confirmed damage. Method #4 confirms the damage.

Method #2: Email New Rows With a Scheduled Task (Free, Automated)

Database Mail sends new suspect pages to your inbox so nobody has to open the table. Microsoft states that Database Mail must be enabled with the Database Mail Configuration Wizard or with sp_configure before sp_send_dbmail works.

• Expand SQL Server Agent in Object Explorer.
• Open the shortcut menu of Jobs and select New Job.
• Type a name like Suspect Pages Check on the General page.
• Go to the Steps page and select New. Set the type to T-SQL and paste the script below.
• Go to the Schedules page and select New. Choose a daily or an hourly schedule.
• Select OK to save the task.

IF EXISTS (SELECT 1 FROM msdb.dbo.suspect_pages AS sp WHERE sp.event_type BETWEEN 1 AND 3)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = N'DBA Mail',
        @recipients = N'dba@example.com',
        @subject = N'Suspect pages found',
        @body = N'Review msdb.dbo.suspect_pages for rows with open event types.',
        @query = N'SELECT DB_NAME(database_id) AS db, file_id, page_id, event_type, error_count, last_update_date FROM msdb.dbo.suspect_pages AS sp WHERE sp.event_type BETWEEN 1 AND 3';
END

• Replace the profile name and the address with your values.
• Microsoft states that the query in @query executes in a separate session so keep local variables out of it.
• Add more addresses with a semicolon between them.
• Make sure the sender belongs to the DatabaseMailUserRole role in msdb.

Note. Rows with open event types remain in the table until a restore or a repair updates them. Email repeats on every schedule until then.

Method #3: Set Alerts for Errors 823, 824 and 825 (Free, Instant)

Alerts about suspect pages reach you the moment the error appears. Microsoft states in the sp_add_alert reference that alerts fire for errors that SQL Server sends to the Windows application log and that errors of severity 19 or higher reach the log. Error 824 shows severity 24 in Microsoft documentation and the error 823 sample shows severity 24 too.

Create the operator first. Execute the statement below to add one alert and one notification for all three error numbers.

USE msdb;
GO
DECLARE @id int, @name sysname;
DECLARE error_ids CURSOR LOCAL FAST_FORWARD FOR
    SELECT v.id FROM (VALUES (823), (824), (825)) AS v(id);
OPEN error_ids;
FETCH NEXT FROM error_ids INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @name = CONCAT(N'Page error ', @id);
    EXEC dbo.sp_add_alert @name = @name, @message_id = @id, @delay_between_responses = 600, @include_event_description_in = 1;
    EXEC dbo.sp_add_notification @operator_name = N'Storage Admins', @alert_name = @name, @notification_method = 1;
    FETCH NEXT FROM error_ids INTO @id;
END
CLOSE error_ids;
DEALLOCATE error_ids;

• Permission. Microsoft limits sp_add_alert to the sysadmin role by default.
• Operator. Replace Storage Admins with the name of your operator.
• Delay. The value 600 waits ten minutes between responses.
• Database. Add @database_name to limit an alert to one database. Without it the alert fires for errors from any database.

Only logged messages cause an alert. Confirm that SQL Server logs the three messages with the statement below and treat a 0 in is_event_logged as a message without an alert.

SELECT message_id, is_event_logged, severity
FROM sys.messages
WHERE language_id = 1033 AND message_id BETWEEN 823 AND 825;

Note. Alerts watch for the errors and not for the table. Add Method #1 or #2 as well so you see suspect pages that never raised a message.

Method #4: Confirm With DBCC CHECKDB and Restore the Pages (Free, Needs Backups)

Rows in the table mark suspect pages and DBCC CHECKDB confirms the damage. Microsoft states in the error 823 guidance that a clean CHECKDB result suggests an intermittent system problem or a disk problem.

• Execute DBCC CHECKDB (N'BookingDB') WITH NO_INFOMSGS in SSMS.
• Match the pages in the output with the rows of Method #1.
• Build the page list for the restore with the statement below.

SELECT CONCAT(file_id, ':', page_id) AS page_ref
FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID(N'BookingDB') AND event_type BETWEEN 1 AND 3;

Microsoft explains page restores with these conditions:

• Recovery Model. Page restore applies to the full and bulk logged models and Microsoft advises the full model with a log backup.
• Editions. Offline page restores work in all editions while online page restores need Enterprise edition.
• Page Count. A file restore is more efficient when more than a few pages in a file are damaged.
• Limits. Page restore cannot replace the transaction log, page 0, the database boot page or the GAM, SGAM and PFS allocation pages.
• Permissions. RESTORE permissions default to the sysadmin and dbcreator roles and to the database owner.

RESTORE DATABASE BookingDB PAGE = '1:318, 1:319'
   FROM DISK = N'E:\Backup\BookingDB_full.bak'
   WITH NORECOVERY;

Apply every log backup with RESTORE LOG and NORECOVERY. Then take a tail log backup and restore it WITH RECOVERY as in the Microsoft example. Our Logical Consistency I/O Error Guide lists the Object Explorer path for the same restore.

Microsoft states that a restore marks the page entries as restored so event type 4 appears after the restore.

Note. DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS marks deallocated pages as type 7. Event type 7 means rows were removed so compare the table counts with your last backup.

Method #5: Export Damaged File Tables With EmailDoctor (Paid After Demo)

Use recovery software when suspect pages remain damaged and no backup exists for a page or file restore. EmailDoctor SQL Database Recovery scans a damaged MDF file and shows the recovered tables, views and other objects as a preview ahead of the export.

Recovery software works on a copy of the file and cannot repair the disk that damaged the pages. Free demo mode exports 25 records for every table plus 5 more objects.

Open and Scan the Damaged MDF

1. Launch the software from the Start menu once the setup ends.
2. Click Open and choose the saved copy of BookingDB.mdf.
3. Select Quick Scan with Auto detect ticked and the software identifies the version of SQL Server in the file.
4. Close the scan note with OK.
5. Select any table in the tree on the left to preview its records.

Export the Objects to SQL Server

6. Open Export on the toolbar. Then press Export As SQL Server Database.
7. Select your server in the name list. Windows Authentication signs in with your account while SQL Server Authentication asks for a login and a password.
8. Click Create New Database. Type BookingDB_Recovered in the name box.
9. Tick the components to export. Set the mode to With Schema & Data.
10. Start the export with the Export button and wait for the Export Completed message. Select OK to close it.
11. Execute DBCC CHECKDB (N'BookingDB_Recovered') in SSMS. Back up the new database in full.

    Choose Advanced Scan if Quick Scan lists fewer tables than your database contains. Our Corrupt MDF File Repair Guide describes the Quick Scan and Advanced Scan options.

How to Clear Old Suspect Pages Rows?

Microsoft states that admins manage the table mostly by deleting old rows and recommends that you delete or archive rows with the type restored or repaired or rows with an old last_update value. Microsoft names two automatic deletions and they are ALTER DATABASE REMOVE FILE and DROP DATABASE.

Archive the rows first and delete them afterwards. Create the archive table with the same columns in a database of your own.

INSERT INTO DBAdmin.dbo.suspect_pages_archive
SELECT sp.*
FROM msdb.dbo.suspect_pages AS sp
WHERE sp.event_type IN (4, 5, 7) AND sp.last_update_date < DATEADD(DAY, -90, GETDATE());

DELETE sp
FROM msdb.dbo.suspect_pages AS sp
WHERE sp.event_type IN (4, 5, 7) AND sp.last_update_date < DATEADD(DAY, -90, GETDATE());

• Replace the 90 days with your retention period.
• Members of the db_owner role in msdb or the sysadmin role delete rows.
• Expand msdb, Tables and System Tables in Object Explorer to edit rows by hand. Open the shortcut menu of dbo.suspect_pages and select Edit Top 200 Rows.

Troubleshooting Common Suspect Pages Problems

Six problems slow down the monitoring of suspect pages:

• Errors Appear in the Log but the Table Shows No Rows. Microsoft states that a full table logs no new errors so clear old rows with the section above.
• Rows Return After a Cleanup. Microsoft links many new rows to a problem in the I/O subsystem so check the storage and the Windows event log.
• Email Never Arrives. Database Mail is disabled or the profile is missing or the sender lacks the DatabaseMailUserRole role so recheck Method #2.
• Alert Never Fires. SQL Server does not log the message or the operator has no email address so execute the sys.messages query of Method #3.
• Page Restore Fails. The page is an allocation page or the boot page or the database uses the bulk logged model so choose a full restore or Method #5.
• Query Fails With Error 824 During Repair. Microsoft states that automatic page repair is asynchronous and tells applications to retry after errors 823, 824 or 829.

Frequently Asked Questions

Author’s Verdict

Rows in suspect_pages mark suspected pages and not proven damage. Query the table, confirm with DBCC CHECKDB and treat event types 1, 2 and 3 as open problems.

Add the scheduled email and the alerts so you learn about new suspect pages without a manual check. Clear restored and repaired rows on a schedule before the 1,000 row limit stops the logging.

Restore damaged pages from a backup first. Use EmailDoctor recovery software when no backup exists and the file is damaged.