Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?

Let’s find out.

Here is a very basic setup. Using the following query, I will find the page ID I want to ruin. That being page 126.

 DBCC IND (N'ZoraDB', N'people', -1);
GO

I then do some offset calculations and sprinkle some “magic dust”. Then I am ready to run CHECKDB.

DBCC CHECKDB (N'ZoraDB')  WITH NO_INFOMSGS

Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:126) could not be processed.  See other errors for details. CHECKDB found 0 allocation errors and 2 consistency errors in table ‘people’ (object ID 245575913). CHECKDB found 0 allocation errors and 2 consistency errors in database ‘ZoraDB’. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ZoraDB).

It is time to check the suspect_pages table.

USE [msdb]
GO
SELECT * FROM dbo.suspect_pages
ORDER BY last_update_date DESC

Event type 2 means a bad checksum.

Now to the main point of the blog post, let’s run repair (a last resort option) on the database and make sure the database is fit and ready for users then we will go and re-check the suspect table.

ALTER DATABASE [ZoraDB] SET SINGLE_USER;
GO
DBCC CHECKDB    ('ZoraDB'  , REPAIR_ALLOW_DATA_LOSS )
ALTER DATABASE [ZoraDB] SET MULTI_USER;
GO

CHECKDB fixed 0 allocation errors and 2 consistency errors in database ‘ZoraDB’. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

BY THE WAY – It deleted my data. I knew it would because I used REPAIR_ALLOW_DATA_LOSS.

 Anyways, now look at the suspect table.

It does NOT get removed, but the event type gets updated from 2 to 7 where 7 means it was de-allocated by CHECKDB.

I went through the same tests then recovered from backup. Again only the event type gets updated to Event type = 4.

So it is down to us to maintain this table which is limited to 1000 rows. The data will remain within this table so just watch out for the event_type to really understand the status of the page.

Filed under: Admin, CHECKDB, Corruption, SQL SERVER Tagged: Corruption, DBCC CHECKDB, DBCC Page, Internals, SQL server, TSQL