31 Days of Disaster Recovery

Follow the Steps

Now that we know what allocation pages are and how to identify them, we can move on to following the three general steps for handling corruption.

1. 
   Identify the corruption (DBCC CHECKDB)
   
2. 
   Identify the objects and types of objects involved
   
3. 
   Take the appropriate steps to correct
   

Step 1 is to identify the corruption. For today’s exercise, I’m going to my cleverly named sample database PFSCorruption. If I run DBCC CHECKDB on the database, I see that it is corrupted.

DBCC CHECKDB(PFSCorruption)

With No_InfoMsgs, All_ErrorMsgs, TableResults;

That gives us a lot of columns that we don’t need, so I’m going to pare that down to just the really important columns. This will make it easier for us to find the bits we really want. I don’t use this when troubleshooting corruption, this is just to make it easier to find the key info for this demonstration.

Declare @DBCC Table (

Error int,

Level smallint,

State tinyint,

MessageText varchar(2500),

RepairLevel varchar(30) null,

Status tinyint,

DbId int,

DbFragId int,

ObjectId int,

IndexId int,

PartitionId bigint,

AllocUnitId bigint,

RidDbId int,

RidPruId int,

[File] int,

Page int,

Slot int,

RefDbId int,

RefPruId int,

RefFile int,

RefPage int,

RefSlot int,

Allocation bigint)

Insert Into @DBCC

Exec sp_executesql N'DBCC CHECKDB(PFSCorruption)

With No_InfoMsgs, All_ErrorMsgs, TableResults;';
Select Level,

State,

MessageText,

RepairLevel,

ObjectId,

IndexId,

[File],

Page,

RefFile,

RefPage

From @DBCC;

Level	State	MessageText	RepairLevel	ObjectId	IndexId	File	Page	RefFile	RefPage
16	5	Database error: Page (1:6) is marked with the wrong type in PFS page (1:1). PFS status 0xa expected 0x44.	NULL	0	-1	1	6	1	1
16	5	Database error: Page (1:7) is marked with the wrong type in PFS page (1:1). PFS status 0xa expected 0x44.	NULL	0	-1	1	7	1	1
10	1	CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.	NULL	0	0	0	0	0	0
10	1	CHECKDB found 2 allocation errors and 0 consistency errors in database ‘PFSCorruption’.	NULL	0	0	0	0	0	0

Identify the Objects

Again, we need to identify the real errors, and those are the ones that are Level 16. The higher the level, the more severe that error is so the higher level errors are the reals one we need to examine. Also, State 5 means that an unknown error occurred that terminated DBCC execution. Each of the 2 error messages mentions 2 different pages. The error messages can be misleading and make you think that pages (1, 6) and (1, 7) are corrupted, but the corruption is page (1, 1). Page (1, 1) has the wrong values for these two pages. So the columns we are interested in this time are RefFile and RefPage rather than File and Page.

We don’t need to do the math in this case. We already know that page 1 is the PFS page and in fact, the error message even called out that it is the PFS page. We know it’s an allocation page, so we know what action is appropriate.

Taking Appropriate Action

I said earlier that allocation pages cannot be repaired or restored. We need to do a full database restore. At this point, I would check my backup situation, back up the tail of the log, and then begin my restore process. If you don’t have backups to restore from, you will need to export the data, create a new version of the database, and re-import the data. It’s not going to be a fast process. There are a few people in the world that could hack the page with a hex editor to repair it manually, but that’s not something we are going to try to demonstrate here. If you try to do that, I highly advise you to make a copy of the files, and try to fix the copy of it.

Sample corrupt database and scripts: PFSCorruption.zip (116 KB)