I was developing course materials for a workshop recently and I wanted to run a lab where you DBCC’d a database, found an error and then worked out how to fix it. The only problem was deliberately corrupting a page in a specific index. I did some investigating and thought I’d share my findings:
If you just need a broken database to play with then you can download one from Paul Randal’s old blog here: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/17/example-corrupt-database-to-play-with.aspx
Tony Rogerson has a good blog here on how to corrupt data without using a Hex Editor:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx
My needs however where a bit different because I needed to corrupt a page in a specific non-clustered index in a database that my customer gave me to develop the workshop with. I’ll use AdventureWorks to illustrate...
First of all you need a Hex Editor because you’re going to change some hex values in the mdf file of the database. I used Hex Workshop which you can download from http://www.hexworkshop.com and use free for 35 days. If you stop SQL Server and load AdventureWorks_Data.mdf into the editor you’ll see a stream of hex and no easy way to see what’s what. Close the file and restart SQL Server.
Now change the PAGE_VERIFY option to NONE so that the checksum being different doesn’t intercept with an error before we can look at the DBCC results.
ALTER DATABASE AdventureWorks SET PAGE_VERIFY NONE
You’re going to be corrupting the AK_CustomerAddress_rowguid non-clustered index on the Sales.CustomerAddress table so the first thing we need is a page number that contains data for that index. I found the solution on page 218 of Kalen Delaney’s Inside SQL Server 2005 Storage Engine book.
SELECT object_name(object_id) AS name, index_id,rows, type_desc AS page_type_desc, total_pages AS pages, first_page
FROM sys.partitions p JOIN sys.system_internals_allocation_units a
ON p.partition_id = a.container_id
WHERE object_id=object_id('sales.customeraddress')
This gives you:
name index_id rows page_type_desc pages first_page
---------------- -------- ----- -------------- ----- --------------
CustomerAddress 1 19220 IN_ROW_DATA 121 0xA02800000100
CustomerAddress 2 19220 IN_ROW_DATA 81 0x381200000100
There is only a clustered index and the non-clustered index on the table so the second row is the one you want. Now back to Kalen’s book to translate the hex into a more familiar page reference:
create function [dbo].[covert_page_nums] (@page_num binary(6))
RETURNS varchar(11)
AS
BEGIN
RETURN(convert(varchar(2), (convert(int, substring(@page_num, 6, 1)) * power(2, 8)) +
(convert(int, substring(@page_num, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(@page_num, 4, 1)) * power(2, 24)) +
(convert(int, substring(@page_num, 3, 1)) * power(2, 16)) +
(convert(int, substring(@page_num, 2, 1)) * power(2, 8)) +
(convert(int, substring(@page_num, 1, 1)))) )
END
SELECT dbo.convert_page_nums(0x381200000100)
Gives you the starting page of the index as 1:4664
Now that you know where the index is you can use DBCC PAGE (described here: https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx ) to see what’s on it. You know from the previous scripts that the index covers 81 pages so add 1 to the starting page to put you into the index tree.
DBCC TRACEON (3604, -1)
GO
DBCC PAGE (AdventureWorks, 1, 4665, 1)
The important output here is the content of Slot 0 (the first row on the page) and Slot 298 (the last row on the page):
Slot 0, Offset 0x60, Length 25, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes =
Memory Dump @0x61C4C060
00000000: 06852aa3 b42f26c8 4988b004 3267c944 †..*../&.I...2g.D
00000010: caae2b00 004e2f00 00†††††††††††††††††..+..N/..
Slot 298, Offset 0x1d7a, Length 25, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes =
Memory Dump @0x61C4DD7A
00000000: 0613f0ce 88184df0 4d8b5508 3d90ba87 †......M.M.U.=...
00000010: e71e5800 008f3c00 00†††††††††††††††††..X...<..
Now you can stop SQL Server and load the mdf file into the Hex Editor.
Search for 06852aa3. The first result takes you to offset 38215776 (decimal) and the subsequent hex values exactly match what you know of the contents of the first row from the DBCC PAGE results. Continuing the search reveals no other exact pattern matches.
Search for 0613f0ce. The first result takes you to 38223226 (decimal) for another exact match and there are no other instances of value.
You now have the beginning and end of the page you want to corrupt and all that’s left to do is to overwrite the data in between with zero’s. In Hex Workshop you can highlight the data and press <Ctrl-I> to overwrite it all. Save the file and restart SQL Server.
Now run DBCC CHECKDB (AdventureWorks) and you’ll see:
Msg 8928, Level 16, State 1, Line 1
Object ID 725577623, index ID 2, partition ID 72057594048872448, alloc unit ID 72057594054311936 (type In-row data): Page (1:4665) could not be processed. See other errors for details.
Msg 8941, Level 16, State 55, Line 1
Table error: Object ID 725577623, index ID 2, partition ID 72057594048872448, alloc unit ID 72057594054311936 (type In-row data), page (1:4665). Test (dbccAuditRecSucceeded == TRUE) failed. Slot 0, offset 0x60 is invalid.
Horray, mission accomplished ! J You can even back it up and add the restore into your demo/lab steps.
Imagine this was your production database, what would you do? Restore? DBCC CHECKDB REPAIR_REBUILD? They’d both work but they’re unnecessary overkill in this situation. Find out what the index is first:
SELECT object_name(object_id)AS 'table',name,index_id,type_desc FROM sys.indexes
WHERE object_id = '725577623'
AND index_id = 2
Results
table name index_id type_desc
----------------- ---------------------------- -------- ------------
CustomerAddress AK_CustomerAddress_rowguid 2 NONCLUSTERED
Its a nonclustered index so we can rebuild it!
ALTER INDEX AK_CustomerAddress_rowguid ON Sales.CustomerAddress REBUILD
And now CHECKDB runs without error. Hooray, you’ve just saved the day!
Christian
Christian Bolton
http://coeo.com