Corruption Challenge 4 – Second Alternate Solution.

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.

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.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!