Steve Stedman’s DBCC Challenge, Week-6 Winning Solution

Week 6 of the Database Corruption Challenge (DBCC), winning solution by @SQLDoubleG *First published @ Steve Stedman’s blog (2015/05/27)

If you are looking for more info on the corrupt database with Week 6 of the Database Corruption Challenge, you can take a look at the original post.

Week 6 was won by Raul Gonzalez who submitted his winning solution just 45 minutes after the challenge began. Raul has solved 4 of the 6 challenges so far, and has scored 2 extra points for linking to the challenge, and commenting on the CheckDB post.

The solution this week was in the non-clustered index, and there was some corruption in the clustered index. If you were to just drop and recreate the non-clustered index, the corruption in the clustered index was not able to be found. Comparing the values in the non-clustered index to the clustered index show where the corruption exists.

Let’s take a look at his solution. The only thing I changes was the directory paths on the restore to match my configuration.

USE master
GO
IF DB_ID('CorruptionChallenge6') IS NOT NULL BEGIN
    ALTER DATABASE CorruptionChallenge6 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE CorruptionChallenge6
END
GO
 
IF OBJECT_ID('tempdb..#fromIndex') IS NOT NULL  DROP TABLE #fromIndex
IF OBJECT_ID('tempdb..#fromTable') IS NOT NULL  DROP TABLE #fromTable
IF OBJECT_ID('tempdb..#goodData')  IS NOT NULL  DROP TABLE #goodData
 
RESTORE FILELISTONLY
FROM DISK = 'C:\DBBackups\CorruptionChallenge6.bak'
GO
 
RESTORE DATABASE CorruptionChallenge6
FROM DISK = 'C:\DBBackups\CorruptionChallenge6.bak'
WITH NORECOVERY, REPLACE
    , MOVE 'CorruptionChallenge6' TO 'C:\SQL_DATA\CorruptionChallenge6.mdf'
    , MOVE 'CorruptionChallenge6_log' TO 'C:\SQL_DATA\CorruptionChallenge6_log.ldf'
GO
 
RESTORE DATABASE CorruptionChallenge6 WITH RECOVERY
GO

-- Custom SP
EXECUTE DBA.dbo.DBA_tableDescription 'CorruptionChallenge6', NULL, NULL, NULL, 1
GO
--databaseName schemaName tableName tableType row_count TotalSpaceMB DataSpaceMB IndexSpaceMB UnusedSpaceMB LastUserAccess TotalUserAccess TableTriggers TableDescription
--CorruptionChallenge6 dbo Customers CLUSTERED 254031 8.227 8.188 0.039 0.030
 
-- Custom SP
EXECUTE DBA.dbo.DBA_indexDescription 'CorruptionChallenge6'
GO
--dbname tableName index_name index_type filegroup_desc is_primary_key row_count size_MB fill_factor reserved_MB data_compression_desc user_seeks user_scans user_lookups user_updates index_columns included_columns filter
--[CorruptionChallenge6] [dbo].[Customers] PK_Customers CLUSTERED PRIMARY Yes 254031 8.23 0 8.26 NONE NULL NULL NULL NULL [id] ASC NULL NULL
--[CorruptionChallenge6] [dbo].[Customers] ncFName NONCLUSTERED PRIMARY No 254031 6.88 0 7.52 NONE NULL NULL NULL NULL [FirstName] ASC (NULL), [LastName] ASC (NULL) NULL NULL
 
USE CorruptionChallenge6;
 
SELECT COUNT(1) AS check1,
COUNT(DISTINCT FirstName) AS check2,
COUNT(DISTINCT MiddleName) AS check3,
COUNT(DISTINCT LastName) AS check4,
CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
CHECKSUM_AGG(CHECKSUM(LastName)) AS check7
FROM [Customers];
--check1 check2 check3 check4 check5 check6 check7
--254031 17 17 879 -8736600 179 1330080960

DBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS
--Msg 8938, Level 16, State 2, Line 49
--Table error: Page (1:1849), Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Unexpected page type 1.
--Msg 8976, Level 16, State 1, Line 49
--Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1849) was not seen in the scan although its parent (1:1832) and previous (1:1848) refer to it. Check any previous errors.
--Msg 8978, Level 16, State 1, Line 49
--Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1850) is missing a reference from previous page (1:1849). Possible chain linkage problem.

USE CorruptionChallenge6
 
SELECT COUNT(1) AS check1,
 COUNT(DISTINCT FirstName) AS check2,
 COUNT(DISTINCT LastName) AS check4,
 CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
 CHECKSUM_AGG(CHECKSUM(LastName)) AS check7
 FROM [Customers]
 WITH (INDEX = 2);
--check1    check2  check4  check5      check7
--254031    17      879     1046491532  1330080960
-- same     same    same    different   same

SELECT c.id, FirstName, LastName, CHECKSUM(c.id, c.FirstName, c.LastName) AS rowCheckSum
INTO #fromIndex
    FROM [Customers] AS c WITH (INDEX = 2)
-- 254031
 
SELECT c.id, FirstName, LastName, CHECKSUM(c.id, c.FirstName, c.LastName) AS rowCheckSum
INTO #fromTable
    FROM [Customers] AS c WITH (INDEX = 1)
-- 254031
 
SELECT * FROM #fromIndex AS i
    INNER JOIN #fromTable AS t
        ON t.id = i.id
            AND t.rowCheckSum = i.rowCheckSum
-- 254029
 
SELECT * FROM #fromIndex AS i
    INNER JOIN #fromTable AS t
        ON t.id = i.id
            AND t.rowCheckSum <> i.rowCheckSum
-- and the 2 different to make 254031
--id        FirstName   LastName    rowCheckSum id      FirstName   LastName    rowCheckSum
--252971    Mia         MULLEN      -915392973  252971  €ea         MULLEN      -875026817
--992       Alexander   FISCHER     1976501021  992     Aliwander   FISCHER     1724842269

-- So index look correct and table not.
 
-- Lets update the table with the correct data
 
UPDATE c
    SET c.FirstName = i.FirstName
    FROM dbo.Customers AS c
        INNER JOIN #fromIndex AS i
            ON i.id = c.id
                AND i.id IN (252971, 992)
--Msg 8630, Level 16, State 1, Line 98
--Internal Query Processor Error: The query processor encountered an unexpected error during execution (HRESULT = 0x80040e19).
-- wow :)

-- Get all good data in a temp table
SELECT c.id, i.FirstName, c.MiddleName, i.LastName
    INTO #goodData
    FROM dbo.Customers AS c
        INNER JOIN #fromIndex AS i
            ON i.id = c.id
 
TRUNCATE TABLE dbo.Customers
 
DBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS
-- No corruption
 
SET IDENTITY_INSERT dbo.Customers ON
 
INSERT INTO dbo.Customers (id, FirstName, MiddleName, LastName)
SELECT id, FirstName, MiddleName, LastName
    FROM #goodData
 
SET IDENTITY_INSERT dbo.Customers OFF
 
-- final check
SELECT COUNT(1) AS check1,
       COUNT(DISTINCT FirstName) AS check2,
       COUNT(DISTINCT MiddleName) AS check3,
       COUNT(DISTINCT LastName) AS check4,
       CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
       CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
       CHECKSUM_AGG(CHECKSUM(LastName)) AS check7
  FROM [Customers];
 
--check1    check2  check3  check4  check5      check6  check7
--254031    17      17      879     1046491532  179     1330080960

SELECT @@VERSION
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
--  Feb 20 2014 20:04:26
--  Copyright (c) Microsoft Corporation
--  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
 
-- Clean up
IF OBJECT_ID('tempdb..#fromIndex') IS NOT NULL  DROP TABLE #fromIndex
IF OBJECT_ID('tempdb..#fromTable') IS NOT NULL  DROP TABLE #fromTable
IF OBJECT_ID('tempdb..#goodData') IS NOT NULL   DROP TABLE #goodData

And that is how Raul Gonzalez won this weeks Database Corruption Challenge.

Awesome work Raul.