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