31 Days of Disaster Recovery

Identify the Corrupt Objects

After running DBCC CHECKDB, you can also check the msdb database to see the pages that were identified as being corrupted. It will have an entry for each corrupt page and give you only very base information. It does not give you the object ID or the index ID, but it does give you the database ID, file, and page. You can use this to dump the page using DBCC Page and get the additional info from there. We don’t need to do that in most situations though. We have the C CHECKDB output above. There is only one object ID reported, and I use the OBJECT_NAME() function to get its name (FactResellerSales). I also see that the index ID is 2. I don’t need to look up the index. The index ID tells me everything I need to know.

Index ID Mapping

• 
  ID 0 = heap
  
• 
  ID 1 = clustered index
  
• 
  ID > 1 = nonclustered index
  

Take Action

Now that we know we’re dealing with just a nonclustered index, we can take the appropriate action. The easiest way to fix a corrupt nonclustered index is to drop and recreate the index. Since the underlying clustered index or heap isn’t corrupted, it can easily recreate it as the data is all still there. However, we are limited to drop and recreate. If we try to rebuild or create with drop_existing, it will fail. Rebuild and create with drop_existing will scan the existing index and will fail when it hits the corruption.

Now we do need the index name since we are going to be working with it directly. I query sys.indexes to get the object names.

Select Object_Name(object_id) As TableName, name As IndexName

From sys.indexes

Where object_id = 341576255

and index_id = 2

This tells me that the table name is FactResellerSales and the index name is IX_FactResellerSales_CurrencyKey. So just to illustrate that we have to drop and recreate it, let’s try rebuilding it first.

-- Rebuild the index??

Alter Index IX_FactResellerSales_CurrencyKey

On dbo.FactResellerSales

Rebuild;

And that gives us this error:

The statement has been terminated.

Msg 824, Level 24, State 2, Line 2

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:7171; actual 0:0). It occurred during a read of page (1:7171) in database ID 8 at offset 0x00000003806000 in file 'C:\Program FilesMicrosoft SQL ServerMSSQL11.SQL13MSSQLDATAAdventureWorksDW2008R2_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Now let’s try using CREATE INDEX … WITH DROP_EXISTING. For this, I will need to script out the create index statement.

-- Create with drop existing??

Create Index IX_FactResellerSales_CurrencyKey

On dbo.FactResellerSales(CurrencyKey)

With Drop_Existing;

This yields the exact same error as the rebuild statement did. This leaves us with drop and recreate. I write the drop statement followed by the create statement and run that:

-- Drop and create?

Drop Index dbo.FactResellerSales.IX_FactResellerSales_CurrencyKey;
Create Index IX_FactResellerSales_CurrencyKey

On dbo.FactResellerSales(CurrencyKey);

That completed successfully. We should be free of corruption now. So let’s run DBCC CHECKDB again to verify. This time CHECKDB completes successfully with 0 errors.

Summary and Follow-up

As I showed above, some forms of corruption are actually very easy to fix. There’s no reason to start panicking when you get a corruption error message. Just follow the steps defined above and then take the appropriate action. I’ll get into some other types of corruption later in the series. In the meantime, you can download the sample corrupt database I used tonight and the code to step you through fixing it yourself.

Sample database and demo code: CorruptionDemo_AdventureWorksDW2012.zip (12.22 MB)

Day 6: Dealing With Corruption in Allocation Pages

Yesterday, I covered corruption in nonclustered indexes, the easiest type of corruption to handle. Today, I’m going to move on to something slightly more complex, yet still really simple to manage. Today, I’m going to talk about what to do when you encounter corruption of an allocation page. Allocation pages cannot be repaired not can they be single-page restored. If you have corruption of an allocation page, you need to restore the whole database.

What are Allocation Pages?

I’ve talked in great detail previously about allocation pages in my posts on dealing with tempdb contention here (part 1) and here (part 2). Allocation pages are special pages in the data files that track and manage extent allocations. There are 3 types of allocation pages that we are going to focus on today.

Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

• 
  Page ID = 2 or Page ID % 511232
  

Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

• 
  Page ID = 3 or (Page ID – 1) % 511232
  

Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 64 MB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.

• 
  Page ID = 1 or Page ID % 8088
  

If the page ID is 1, 2, or 3, it’s obvious that it’s one of the above allocation pages. If it’s a high number, then you have a couple of ways to figure out what type of page it is. Of course, you can do the math and calculate whether it’s an allocation page or not. I’ve included the below script to do the math for you.

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 way you could determine the page type is to dump the page using DBCC PAGE and look for the m_type value in the header output. The type values are different from the page IDs (m_pageid in DBCC PAGE output).

Page Type	m_type
PFS	11
GAM	8
SGAM	9