Welcome back for day two of this six part series Introduction to Integrity, sponsored by Idera. In this post, I want to dig into the first integrity issue, corruption in allocation pages.

If you run the DBCC CHECKDB command and get errors, I isolate the most important errors (Level 16) and look at the page IDs in the errors. one of the first things I want to know is what type of page is corrupted. As I said in day 1 of the series, the type of page and/or object will help you decide how to proceed. So find the page IDs and figure out what you’re dealing with.

If you download the sample corrupt databases — RecoveringFromCorruption.zip (13.9 MB) — I mentioned on day 1 of the series, it includes a database called PFSCorruption. Below is a screen shot with error information for the failed DBCC CHECKDB you will get from this database. The screenshot is from Idera’s new free tool SQL Integrity Check.

Corruption in Allocation Pages

For the sake of brevity, I’m not going to explain the purpose of the different allocation pages; however, it is important to know how often they repeat throughout the files. If the corruption is in a very high number page, you will have to do some calculations to determine what type of page is affected by the corruption. It is only necessary to calculate this when there is no Object ID reported in the error messages. If it is associated with an object, it is not an allocation page.

The formula for determining the type of page experiencing contention is:

• File Header: Page ID = 0 (does not repeat)
• PFS: Page ID = 1 or Page ID % 8088
• GAM: Page ID = 2 or Page ID % 511232
• SGAM: Page ID = 3 or (Page ID – 1) % 511232
• DCM: Page ID = 6 or (Page ID – 6) % 511232
• BCM: Page ID = 7 or (Page ID – 7) % 511232

The following query will calculate the page ID for you and tell you if it is an allocation page:

Declare @Page int;

Set @Page = <page ID>;

Select PageType = CASE
   WHEN @Page = 0 Then 'File Header Page'
   WHEN @Page = 1 Or @Page % 8088 = 0
       THEN 'PFS Page'
   WHEN @Page = 2 Or @Page % 511232 = 0
       THEN 'GAM Page'
   WHEN @Page = 3 Or (@Page - 1) % 511232 = 0
       THEN 'SGAM Page'
   WHEN @Page = 6 Or (@Page - 6) % 511232 = 0
       THEN 'DCM Page'
   WHEN @Page = 7 Or (@Page - 7) % 511232 = 0
       THEN 'BCM Page'
   ELSE 'Not an allocation page'
END

Once you know that you are dealing with an allocation page, deciding on a course of action is simple. Allocation pages cannot be single-page restored (more on this process later). They cannot be repaired by the CHECKDB repair process. You are facing a restore of the whole database. The good news is that you can back up the tail of the log file (if in full or bulk-logged recovery model) so that you can restore with no data loss. This is assuming that you have good backups to restore from. If you were backing up with the CHECKSUM option like I mentioned previously, the odds of having good backups to restore from goes way up.

If you do not have backups to restore from or you are in simple recovery model and would lose all data since the last full or differential backup, your last remaining option is to try to BCP as much data as possible out of the database, manually recreate the database and all objects, and reimport the data.