{"id":72,"date":"2015-10-10T12:29:05","date_gmt":"2015-10-10T11:29:05","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=72"},"modified":"2015-10-20T21:06:45","modified_gmt":"2015-10-20T20:06:45","slug":"steve-stedmans-dbcc-challenge-week-4-second-alternate-solution","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2015\/10\/10\/steve-stedmans-dbcc-challenge-week-4-second-alternate-solution\/","title":{"rendered":"Steve Stedman\u2019s DBCC Challenge, Week-4 Second Alternate Solution"},"content":{"rendered":"

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

The following is a solution to Corruption Challenge #4<\/a> that uses DBCC WritePage to fix the corruption. \u00a0Before proceeding, be sure to read this warning.<\/p>\n

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.<\/span><\/p>\n

DBCC WritePage is a cool, but extremely risky way to solve this weeks Database Corruption Challenge<\/a>, however, do NOT EVER RUN THIS ON A PRODUCTION SYSTEM.<\/span><\/p>\n

 <\/p>\n

The Solution using DBCC WritePage<\/h3>\n

 <\/p>\n

The following is the actual solution submitted by Raul Gonzalez for Week 4 of the Database Corruption Challenge.<\/p>\n

\r\nUSE master\r\nGO\r\nIF DB_ID('CorruptionChallenge4') IS NOT NULL BEGIN\r\n    ALTER DATABASE CorruptionChallenge4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n    DROP DATABASE CorruptionChallenge4\r\nEND\r\nGO\r\n--RESTORE FILELISTONLY\r\n--FROM DISK = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Backup\\CorruptionChallenge4_Corrupt.bak'\r\n \r\n-- Create folder C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\\r\n-- grant permission to SQL Server service account\r\n \r\nRESTORE DATABASE CorruptionChallenge4\r\nFROM DISK = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Backup\\CorruptionChallenge4_Corrupt.bak'\r\nWITH NORECOVERY, REPLACE\r\n    , MOVE 'CorruptionChallenge4' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\CorruptionChallenge4.mdf'\r\n    , MOVE 'UserObjects' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\CorruptionChallenge4_UserObjects.ndf'\r\n    , MOVE 'CorruptionChallenge4_log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\CorruptionChallenge4_log.ldf'\r\nGO\r\n \r\nRESTORE DATABASE CorruptionChallenge4 WITH RECOVERY\r\nGO\r\n \r\nUSE [CorruptionChallenge4]\r\n \r\nSELECT * FROM dbo.Customers WHERE id IN (510900 ,510901)\r\n-- alocation unit is not correct at page level\r\n \r\n-- Allocation unit for the CIX in the table.\r\nUSE [CorruptionChallenge4]\r\n \r\nSELECT au.allocation_unit_id,  * FROM sys.indexes AS ix\r\n    INNER JOIN sys.partitions AS p\r\n        ON p.object_id = ix.object_id\r\n            AND p.index_id = ix.index_id\r\n    INNER JOIN sys.allocation_units AS au\r\n        ON au.container_id = p.hobt_id\r\nWHERE ix.object_id = OBJECT_ID('dbo.Customers')\r\nAND ix.index_id = 1\r\n \r\n-- this bit is from Paul Randal PASS SUMMIT 2014 DEMO to modify the allocation unit\r\nDECLARE @alloc BIGINT = 72057594039828480; -- Allocation unit recorded in metadata\r\nDECLARE @index BIGINT;\r\nSELECT @index =\r\n    CONVERT (BIGINT,\r\n        CONVERT (FLOAT, @alloc)\r\n            * (1 \/ POWER (2.0, 48)) -- right shift, reciprocal of left shift\r\n    );\r\nSELECT\r\n    CONVERT (BIGINT,\r\n        CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))\r\n            * (1 \/ POWER (2.0, 16)) -- right shift, reciprocal of left shift\r\n    ) AS [m_objId],\r\n    @index AS [m_indexId];\r\nGO\r\n--m_objId   m_indexId\r\n--29    256\r\n \r\n-- These numbers are in decimal\r\n--replace with calculated values\r\n \r\n-- Substitute those values on each data\/index page\r\n-- idobj at offset 24 for 4 bytes, idind at offset 6 for 2 bytes\r\n \r\n-- Use a calculator to convert the numbers to hex, and then byte-reverse them\r\nDBCC WRITEPAGE (N'CorruptionChallenge4', 3, 2150, 24, 4, 0x1D000000);\r\nDBCC WRITEPAGE (N'CorruptionChallenge4', 3, 2150, 6, 2, 0x0001);\r\nGO\r\n \r\n-- After this I can see the data for those rows\r\nUSE [CorruptionChallenge4]\r\n \r\nSELECT * FROM dbo.Customers WHERE id IN (510900 ,510901)\r\n \r\n-- But we have to fix all pages... what about a loop?\r\n-- Get all pages for the index\r\nCREATE TABLE #dbccInd (\r\nID                  INT IDENTITY(1,1)\r\n, PageFID           INT NULL\r\n, PagePID           INT NULL\r\n, IAMFID            INT NULL\r\n, IAMPID            INT NULL\r\n, ObjectID          INT NULL\r\n, IndexID           INT NULL\r\n, PartitionNumber   INT NULL\r\n, PartitionID       BIGINT NULL\r\n, iam_chain_type    SYSNAME NULL\r\n, Pagetype          INT NULL\r\n, IndexLevel        INT NULL\r\n, NextPageFID       INT NULL\r\n, NextPagePID       INT NULL\r\n, PrevPageFID       INT NULL\r\n, PrevPagePID       INT NULL\r\n)\r\nINSERT INTO #dbccInd\r\nEXECUTE sp_executesql N'DBCC IND(''CorruptionChallenge4'', ''dbo.Customers'', 0)'\r\n \r\nDECLARE @count  INT = 1\r\nDECLARE @max    INT = (SELECT MAX(ID) FROM #dbccInd)\r\nDECLARE @writepage  SYSNAME\r\nDECLARE @pageNo INT\r\n \r\nWHILE @count <= @max BEGIN\r\n    SET @pageNo = (SELECT PagePID FROM #dbccInd WHERE ID = @count AND PageType = 1)\r\n \r\n    IF @pageNo IS NULL BEGIN\r\n        SET @count += 1\r\n        CONTINUE\r\n    END\r\n \r\n    SET @writepage = N'DBCC WRITEPAGE (N''CorruptionChallenge4'', 3, ' + CONVERT(VARCHAR, @pageNo) + ', 24, 4, 0x1D000000)'\r\n    EXECUTE sp_executesql @writepage\r\n    SET @writepage = N'DBCC WRITEPAGE (N''CorruptionChallenge4'', 3, ' + CONVERT(VARCHAR, @pageNo) + ', 6, 2, 0x0001)'\r\n    EXECUTE sp_executesql @writepage\r\n \r\n    SET @count += 1\r\nEND\r\n \r\nSELECT * FROM dbo.Customers\r\n-- 511740\r\n \r\nSELECT COUNT(*)\r\nFROM sys.objects\r\nWHERE is_ms_shipped = 0;\r\n-- 5\r\n<\/pre>\n
 
\nEvery data page in the [dbo].[customers] table was corrupt, and corrupt in the exact same way. Raul was able to loop through them all to correct the corruption.<\/p>\n
There is is, that is how Raul completed the Database Corruption Challenge<\/a> this week. Nice work Raul.<\/p>\n
WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Week 4 of the Database Corruption Challenge (DBCC), alternate solution by @SQLDoubleG *First published @ Steve Stedman’s blog (2015\/05\/06) The following is a solution to Corruption Challenge #4 that uses DBCC WritePage to…<\/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\/72"}],"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=72"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/72\/revisions"}],"predecessor-version":[{"id":73,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/72\/revisions\/73"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=72"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=72"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=72"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}