{"id":51,"date":"2015-10-10T15:19:55","date_gmt":"2015-10-10T14:19:55","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=51"},"modified":"2015-10-20T21:07:41","modified_gmt":"2015-10-20T20:07:41","slug":"steve-stedmans-dbcc-challenge-week-6","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2015\/10\/10\/steve-stedmans-dbcc-challenge-week-6\/","title":{"rendered":"Steve Stedman’s DBCC Challenge, Week-6 Winning Solution"},"content":{"rendered":"

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

If you are looking for more info on the corrupt database with Week 6 of the\u00a0Database Corruption Challenge<\/a>, you can take a look at the original post<\/a>.<\/p>\n

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\u00a0points<\/a>\u00a0for linking to the challenge, and commenting on the\u00a0CheckDB<\/a>\u00a0post.<\/p>\n

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.<\/p>\n

Let\u2019s take a look at his solution. The only thing I changes was the directory paths on the restore to match my configuration.<\/p>\n

\r\nUSE master\r\nGO\r\nIF DB_ID('CorruptionChallenge6') IS NOT NULL BEGIN\r\n    ALTER DATABASE CorruptionChallenge6 SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n    DROP DATABASE CorruptionChallenge6\r\nEND\r\nGO\r\n \r\nIF OBJECT_ID('tempdb..#fromIndex') IS NOT NULL  DROP TABLE #fromIndex\r\nIF OBJECT_ID('tempdb..#fromTable') IS NOT NULL  DROP TABLE #fromTable\r\nIF OBJECT_ID('tempdb..#goodData')  IS NOT NULL  DROP TABLE #goodData\r\n \r\nRESTORE FILELISTONLY\r\nFROM DISK = 'C:\\DBBackups\\CorruptionChallenge6.bak'\r\nGO\r\n \r\nRESTORE DATABASE CorruptionChallenge6\r\nFROM DISK = 'C:\\DBBackups\\CorruptionChallenge6.bak'\r\nWITH NORECOVERY, REPLACE\r\n    , MOVE 'CorruptionChallenge6' TO 'C:\\SQL_DATA\\CorruptionChallenge6.mdf'\r\n    , MOVE 'CorruptionChallenge6_log' TO 'C:\\SQL_DATA\\CorruptionChallenge6_log.ldf'\r\nGO\r\n \r\nRESTORE DATABASE CorruptionChallenge6 WITH RECOVERY\r\nGO\r\n<\/pre>\n
\"Week6Step6_Database_Corruption\"<\/a><\/p>\n
\r\n-- Custom SP\r\nEXECUTE DBA.dbo.DBA_tableDescription 'CorruptionChallenge6', NULL, NULL, NULL, 1\r\nGO\r\n--databaseName schemaName tableName tableType row_count TotalSpaceMB DataSpaceMB IndexSpaceMB UnusedSpaceMB LastUserAccess TotalUserAccess TableTriggers TableDescription\r\n--CorruptionChallenge6 dbo Customers CLUSTERED 254031 8.227 8.188 0.039 0.030\r\n \r\n-- Custom SP\r\nEXECUTE DBA.dbo.DBA_indexDescription 'CorruptionChallenge6'\r\nGO\r\n--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\r\n--[CorruptionChallenge6] [dbo].[Customers] PK_Customers CLUSTERED PRIMARY Yes 254031 8.23 0 8.26 NONE NULL NULL NULL NULL [id] ASC NULL NULL\r\n--[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\r\n \r\nUSE CorruptionChallenge6;\r\n \r\nSELECT COUNT(1) AS check1,\r\nCOUNT(DISTINCT FirstName) AS check2,\r\nCOUNT(DISTINCT MiddleName) AS check3,\r\nCOUNT(DISTINCT LastName) AS check4,\r\nCHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,\r\nCHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,\r\nCHECKSUM_AGG(CHECKSUM(LastName)) AS check7\r\nFROM [Customers];\r\n--check1 check2 check3 check4 check5 check6 check7\r\n--254031 17 17 879 -8736600 179 1330080960\r\n<\/pre>\n
\"Week6Step2_Database_Corruption\"<\/a><\/p>\n
\r\nDBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS\r\n--Msg 8938, Level 16, State 2, Line 49\r\n--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.\r\n--Msg 8976, Level 16, State 1, Line 49\r\n--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.\r\n--Msg 8978, Level 16, State 1, Line 49\r\n--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.\r\n<\/pre>\n
\"Week6Step3_Database_Corruption\"<\/a><\/p>\n
\r\nUSE CorruptionChallenge6\r\n \r\nSELECT COUNT(1) AS check1,\r\n COUNT(DISTINCT FirstName) AS check2,\r\n COUNT(DISTINCT LastName) AS check4,\r\n CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,\r\n CHECKSUM_AGG(CHECKSUM(LastName)) AS check7\r\n FROM [Customers]\r\n WITH (INDEX = 2);\r\n--check1    check2  check4  check5      check7\r\n--254031    17      879     1046491532  1330080960\r\n-- same     same    same    different   same\r\n<\/pre>\n
\"Week6Step4_Database_Corruption\"<\/a><\/p>\n
\r\nSELECT c.id, FirstName, LastName, CHECKSUM(c.id, c.FirstName, c.LastName) AS rowCheckSum\r\nINTO #fromIndex\r\n    FROM [Customers] AS c WITH (INDEX = 2)\r\n-- 254031\r\n \r\nSELECT c.id, FirstName, LastName, CHECKSUM(c.id, c.FirstName, c.LastName) AS rowCheckSum\r\nINTO #fromTable\r\n    FROM [Customers] AS c WITH (INDEX = 1)\r\n-- 254031\r\n \r\nSELECT * FROM #fromIndex AS i\r\n    INNER JOIN #fromTable AS t\r\n        ON t.id = i.id\r\n            AND t.rowCheckSum = i.rowCheckSum\r\n-- 254029\r\n \r\nSELECT * FROM #fromIndex AS i\r\n    INNER JOIN #fromTable AS t\r\n        ON t.id = i.id\r\n            AND t.rowCheckSum <> i.rowCheckSum\r\n-- and the 2 different to make 254031\r\n--id        FirstName   LastName    rowCheckSum id      FirstName   LastName    rowCheckSum\r\n--252971    Mia         MULLEN      -915392973  252971  \u20acea         MULLEN      -875026817\r\n--992       Alexander   FISCHER     1976501021  992     Aliwander   FISCHER     1724842269\r\n<\/pre>\n
\"Week6Step5_Database_Corruption\"<\/a><\/p>\n
\r\n-- So index look correct and table not.\r\n \r\n-- Lets update the table with the correct data\r\n \r\nUPDATE c\r\n    SET c.FirstName = i.FirstName\r\n    FROM dbo.Customers AS c\r\n        INNER JOIN #fromIndex AS i\r\n            ON i.id = c.id\r\n                AND i.id IN (252971, 992)\r\n--Msg 8630, Level 16, State 1, Line 98\r\n--Internal Query Processor Error: The query processor encountered an unexpected error during execution (HRESULT = 0x80040e19).\r\n-- wow :)\r\n<\/pre>\n
\"Week6Step6_Database_Corruption\"<\/a><\/p>\n
\r\n\r\n-- Get all good data in a temp table\r\nSELECT c.id, i.FirstName, c.MiddleName, i.LastName\r\n    INTO #goodData\r\n    FROM dbo.Customers AS c\r\n        INNER JOIN #fromIndex AS i\r\n            ON i.id = c.id\r\n \r\nTRUNCATE TABLE dbo.Customers\r\n \r\nDBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS\r\n-- No corruption\r\n \r\nSET IDENTITY_INSERT dbo.Customers ON\r\n \r\nINSERT INTO dbo.Customers (id, FirstName, MiddleName, LastName)\r\nSELECT id, FirstName, MiddleName, LastName\r\n    FROM #goodData\r\n \r\nSET IDENTITY_INSERT dbo.Customers OFF\r\n \r\n-- final check\r\nSELECT COUNT(1) AS check1,\r\n       COUNT(DISTINCT FirstName) AS check2,\r\n       COUNT(DISTINCT MiddleName) AS check3,\r\n       COUNT(DISTINCT LastName) AS check4,\r\n       CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,\r\n       CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,\r\n       CHECKSUM_AGG(CHECKSUM(LastName)) AS check7\r\n  FROM [Customers];\r\n \r\n--check1    check2  check3  check4  check5      check6  check7\r\n--254031    17      17      879     1046491532  179     1330080960\r\n\r\n<\/pre>\n
\r\nSELECT @@VERSION\r\n-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)\r\n--  Feb 20 2014 20:04:26\r\n--  Copyright (c) Microsoft Corporation\r\n--  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)\r\n \r\n-- Clean up\r\nIF OBJECT_ID('tempdb..#fromIndex') IS NOT NULL  DROP TABLE #fromIndex\r\nIF OBJECT_ID('tempdb..#fromTable') IS NOT NULL  DROP TABLE #fromTable\r\nIF OBJECT_ID('tempdb..#goodData') IS NOT NULL   DROP TABLE #goodData\r\n<\/pre>\n
And that is how Raul Gonzalez won this weeks Database Corruption Challenge.<\/p>\n
Awesome work Raul.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[11,13,12],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/51"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=51"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/51\/revisions"}],"predecessor-version":[{"id":84,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/51\/revisions\/84"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}