SQL Server disaster recovery: 31-6th days: management area allocation page damage handling

description: the Article In the disaster recovery series is composed of Robert Davis , released in sqlsoldier, I personally think it is quite good, so according to my own understanding, testing and sorting are not directly translated. If there is any inaccuracy, please correct me.

This article is the fifth article on Database disaster recovery. The previous article demonstrates how to fix simple non-clustered index damages. Today, we will look at a more complex one, that is, the management area allocation page is damaged, however, the distribution page cannot be repaired and the entire database can only be recovered.

What is the management area allocation page??
The management area allocation page is a special page in the data file for tracking and management area allocation. This article will focus on three types:

Global allocation ing table (GAM): records the allocated areas. For a data file, each 4 GB has a gam page with a ID of 2 and then appears on every 511,232 pages.
Page id = 2 or page ID %511232

Shared global allocation ing table (SGAM): records the areas that are currently used as a hybrid zone and have at least one unused page. Each 4 GB has a SGAM page, and its ID is always 3, it appears on every 511,232 pages.
Page id = 3 or (page ID-1) % 511232

Page space available (PFS): records the allocation status of each page, whether a single page has been allocated and the amount of available space per page. Each 64 MB has an PFS page, its ID is always 1, and then appears on every 8,088 pages.
Page id = 1 or page ID %8088

If the page ID is 1/2/3, it is obvious that you will know what management area the page is. If the page ID is large, there are two ways to distinguish them:

One way is to use SQL script computing:

 Declare   @ Pageid   Int  ;  --  Enter page number  --  E.g., 8088 = PFS page  Set  @ Pageid   =   8088  ;  Select   Case      When   @ Pageid   =   1   Or   @ Pageid   %   8088   =   0  Then   '  Is PFS page  '      When   @ Pageid   =   2   Or   @ Pageid   %   511232   =   0   Then   '  Is Gam page '      When   @ Pageid   =   3   Or ( @ Pageid   -   1 ) %   511232   =   0   Then   '  Is SGAM page '      Else   '  Is not PFS, GAM, or SGAM page  '      End 

Another method is to use DBCC page to view its m_type value:

Now follow the steps below:
Now we know which are the management area allocation pages, and we will proceed in three steps:

1. Determine the damage (using DBCC checkdb)
2. Determine the damaged objects and object types (such as index pages and allocation pages)
3. determine the appropriate solution

Confirm damage
First, download a corrupted PFS database and run DBCC checkdb:

 
DBCCCheckdb (pfs0000uption)WithNo_infomsgs, all_errormsgs, tableresults;
 

Because too many columns are returned that we don't need, in order to better reflect the demonstration effect here, minus some columns, we still need to return all columns in actual troubleshooting, here is only for 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 (pfs0000uption) with no_infomsgs, all_errormsgs, tableresults; '  ;  Select   Level  , State, messagetext, repairlevel, objectid, indexid,  [  File  ]  , Page, reffile, refpage  From   @ DBCC ;

Result:

Identify damaged objects
In this step, we need to confirm the real error. We can see that there is a higher level error number 16, which needs to be carefully checked, in addition, column 5 indicates that DBCC checkdb is terminated due to an unknown error, and different pageid () and () in the other two rows) it may mislead you to think that they are corrupt pages, but there are actually two error page values in (), so you need to pay attention to the reffile and refpage columns, instead of file and page columns.

From the result of DBCC checkdb above, we can see that it is a PFS page, so you do not need to remember or judge it by DBCC page, because the result of DBCC is obvious.

Adopt appropriate methods
At the beginning, we have already said that the management area allocation page cannot be repaired or restored, so we have to restore the entire database, so we need to check the backup, especially when the transaction log is backed up and then restored. If there is no backup, you need to import the data to a new database. If you can manually use a hexadecimal editor to fix it (not Nb or ds), You 'd better copy it first for this operation.

Summary
This article mainly describes how to determine the specific corrupt objects, mainly to determine the GAM/SGAM/PFS page of the management area allocation page, but unfortunately they cannot be repaired directly.

Download the database and code in this example

SQL Server disaster recovery 31-7th days: disaster recovery serviceLevel Agreement(Data recovery point objective and data recovery time point objective)