Steve Stedman’s DBCC Challenge, Week-4 Second Alternate Solution

oct

2015

Steve Stedman’s DBCC Challenge, Week-4 Second Alternate Solution

*First published @ Steve Stedman’s blog (2015/05/06)

The following is a solution to Corruption Challenge #4 that uses DBCC WritePage to fix the corruption. Before proceeding, be sure to read this warning.

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.

DBCC WritePage is a cool, but extremely risky way to solve this weeks Database Corruption Challenge, however, do NOT EVER RUN THIS ON A PRODUCTION SYSTEM.

The Solution using DBCC WritePage

The following is the actual solution submitted by Raul Gonzalez for Week 4 of the Database Corruption Challenge.

USE master
GO
IF DB_ID('CorruptionChallenge4') IS NOT NULL BEGIN
    ALTER DATABASE CorruptionChallenge4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE CorruptionChallenge4
END
GO
--RESTORE FILELISTONLY
--FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\Backup\CorruptionChallenge4_Corrupt.bak'
 
-- Create folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
-- grant permission to SQL Server service account
 
RESTORE DATABASE CorruptionChallenge4
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\Backup\CorruptionChallenge4_Corrupt.bak'
WITH NORECOVERY, REPLACE
    , MOVE 'CorruptionChallenge4' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\CorruptionChallenge4.mdf'
    , MOVE 'UserObjects' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\CorruptionChallenge4_UserObjects.ndf'
    , MOVE 'CorruptionChallenge4_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\CorruptionChallenge4_log.ldf'
GO
 
RESTORE DATABASE CorruptionChallenge4 WITH RECOVERY
GO
 
USE [CorruptionChallenge4]
 
SELECT * FROM dbo.Customers WHERE id IN (510900 ,510901)
-- alocation unit is not correct at page level
 
-- Allocation unit for the CIX in the table.
USE [CorruptionChallenge4]
 
SELECT au.allocation_unit_id,  * FROM sys.indexes AS ix
    INNER JOIN sys.partitions AS p
        ON p.object_id = ix.object_id
            AND p.index_id = ix.index_id
    INNER JOIN sys.allocation_units AS au
        ON au.container_id = p.hobt_id
WHERE ix.object_id = OBJECT_ID('dbo.Customers')
AND ix.index_id = 1
 
-- this bit is from Paul Randal PASS SUMMIT 2014 DEMO to modify the allocation unit
DECLARE @alloc BIGINT = 72057594039828480; -- Allocation unit recorded in metadata
DECLARE @index BIGINT;
SELECT @index =
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc)
            * (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
    );
SELECT
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
            * (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
    ) AS [m_objId],
    @index AS [m_indexId];
GO
--m_objId   m_indexId
--29    256
 
-- These numbers are in decimal
--replace with calculated values
 
-- Substitute those values on each data/index page
-- idobj at offset 24 for 4 bytes, idind at offset 6 for 2 bytes
 
-- Use a calculator to convert the numbers to hex, and then byte-reverse them
DBCC WRITEPAGE (N'CorruptionChallenge4', 3, 2150, 24, 4, 0x1D000000);
DBCC WRITEPAGE (N'CorruptionChallenge4', 3, 2150, 6, 2, 0x0001);
GO
 
-- After this I can see the data for those rows
USE [CorruptionChallenge4]
 
SELECT * FROM dbo.Customers WHERE id IN (510900 ,510901)
 
-- But we have to fix all pages... what about a loop?
-- Get all pages for the index
CREATE TABLE #dbccInd (
ID                  INT IDENTITY(1,1)
, PageFID           INT NULL
, PagePID           INT NULL
, IAMFID            INT NULL
, IAMPID            INT NULL
, ObjectID          INT NULL
, IndexID           INT NULL
, PartitionNumber   INT NULL
, PartitionID       BIGINT NULL
, iam_chain_type    SYSNAME NULL
, Pagetype          INT NULL
, IndexLevel        INT NULL
, NextPageFID       INT NULL
, NextPagePID       INT NULL
, PrevPageFID       INT NULL
, PrevPagePID       INT NULL
)
INSERT INTO #dbccInd
EXECUTE sp_executesql N'DBCC IND(''CorruptionChallenge4'', ''dbo.Customers'', 0)'
 
DECLARE @count  INT = 1
DECLARE @max    INT = (SELECT MAX(ID) FROM #dbccInd)
DECLARE @writepage  SYSNAME
DECLARE @pageNo INT
 
WHILE @count <= @max BEGIN
    SET @pageNo = (SELECT PagePID FROM #dbccInd WHERE ID = @count AND PageType = 1)
 
    IF @pageNo IS NULL BEGIN
        SET @count += 1
        CONTINUE
    END
 
    SET @writepage = N'DBCC WRITEPAGE (N''CorruptionChallenge4'', 3, ' + CONVERT(VARCHAR, @pageNo) + ', 24, 4, 0x1D000000)'
    EXECUTE sp_executesql @writepage
    SET @writepage = N'DBCC WRITEPAGE (N''CorruptionChallenge4'', 3, ' + CONVERT(VARCHAR, @pageNo) + ', 6, 2, 0x0001)'
    EXECUTE sp_executesql @writepage
 
    SET @count += 1
END
 
SELECT * FROM dbo.Customers
-- 511740
 
SELECT COUNT(*)
FROM sys.objects
WHERE is_ms_shipped = 0;
-- 5

Every data page in the [dbo].[customers] table was corrupt, and corrupt in the exact same way. Raul was able to loop through them all to correct the corruption.

There is is, that is how Raul completed the Database Corruption Challenge this week. Nice work Raul.