oct
10
2015

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

*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

Week6Step6_Database_Corruption

-- 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

Week6Step2_Database_Corruption

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.

Week6Step3_Database_Corruption

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

Week6Step4_Database_Corruption

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

Week6Step5_Database_Corruption

-- 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 :)

Week6Step6_Database_Corruption


-- 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.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.