Week 6 – The Winning Solution – Database Corruption Challenge

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.

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

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!