It’s time to try something new! I was recently challenged to show something in database page with a hex editor for another blog post. I thought, “Hex what? Are we casting spells?”
Let’s start with, “What is a hex editor?” It’s a program that lets you view the binary of a computer file – the 0’s and 1’s that it’s made of. There are many available.
I started with my coworker Kendra Little’s (blog | twitter) blog, Corrupting Databases for Dummies- Hex Editor Edition. I downloaded the hex editor XVI32, as recommended. She gives instructions on how to install the software, create a database, create some objects, and then open the hex editor to break a page.
Note: this should not be done in, near, or even in the same state as a production database.
Beyond the basics of her blog post, here are the steps I went through to view a database page, break it, and fix it.
Breaking the Page
I issue DBCC IND to view a list of pages that belong to a table or index. The syntax for the command is:
T-SQL | |
1 | DBCC IND ('DBName', 'TableName', <indexnumber>); |
DBCC IND ('DBName', 'TableName', <indexnumber>);
I run the command:
T-SQL | |
1 | DBCC IND ('CorruptMe', 'DeadBirdies', 2); |
DBCC IND ('CorruptMe', 'DeadBirdies', 2);
Make note of a file and page number to work with. I issue a DBCC PAGE command to view page 1116. The syntax for the command is:
T-SQL | |
1 | DBCC PAGE( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]); |
DBCC PAGE( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);
I run the command:
T-SQL | |
1 | DBCC PAGE('CorruptMe', 1, 1116, 3); |
DBCC PAGE('CorruptMe', 1, 1116, 3);
Following the steps in Kendra’s blog, I take the database offline, copy the physical file location, and figure out my page offset. I’ll need that information going forward.
The Hex Editor
Next, I open XVI32 and open the file. Well, that’s kind of fun-looking!
Quite frankly, I have no idea what the heck I’m looking at.
To find the page I had selected, I go to Address > Go To and enter my offset value. I’m now seeing the page on the right side. I can tell this because I can see the word “Tweetie” – the data I inserted in the table.
I click on the first T and type X. Apparently I’ve just changed the data in my data file. Oops! I’ve corrupted a data file!
I save the file and go back to SSMS. I bring the database online. That works without a problem. I run a query to view data in that table, which should access the nonclustered index I mangled.
T-SQL | |
1 2 3 4 | USE CorruptMe; GO SELECT birdName FROM dbo.DeadBirdies; |
USE CorruptMe; GO SELECT birdName FROM dbo.DeadBirdies;
I get a terrible error: “SQL Server detected a logical consistency-based I/O error: incorrect checksum”.
What happens if I run a DBCC CHECKDB? More errors!
Yes, I broke a database. On purpose.
But this is good! It gives me the opportunity to play with a hex editor, see what corruption errors look like, and fix them.
Fix them, you say? Yes, fix them!
Fixing the Corrupted File
This is an easy fix. A corrupted nonclustered index can be fixed by dropping and rebuilding.
First, I go to SSMS and expand the database, table, and indexes. I right-click the index and select Script Index As > Drop and Script Index As > Create To > New Query Editor window.
I run the query to drop the index:
T-SQL | |
1 | DROP INDEX dbo.DeadBirdies.ncBirds; |
DROP INDEX dbo.DeadBirdies.ncBirds;
When I run the query again, data is returned! This time, there is no error.
Now, I rebuild the nonclustered index using the Create statement I scripted, and I can still run the query without errors.
T-SQL | |
1 | CREATE NONCLUSTERED INDEX ncBirds ON dbo.DeadBirdies(BirdName); |
CREATE NONCLUSTERED INDEX ncBirds ON dbo.DeadBirdies(BirdName);
What I Learned
Someday, I may need to manually edit a database (or other) file. This is one of those tasks it’s nice to know I’ve done in practice before I ever need to actually do it – I know what to look for and what to do. I won’t need to panic at a critical moment.
Magical! Where is @SQLUnicorn when I need him?
3 Comments
Okay, so we know how to corrupt a nonclustered index, and fix it by dropping the Non-clustered index. What about changing the base data? What will DBCC do for us there? And If we want to change the data, will we need to recalculate the CHECKSUM for the page? or will DBCC do that on our behalf? Will we need to say accept data loss?
Scott, of course there is no clear answer to that on the underlying cluster or heap being corrupted but in a lot of cases, yes on DBCC doing it and allow data loss. I am really torn between even saying that though because all routes should be taken before doing that. Hopefully anyone reading our comments will be the wiser to running REPAIR_ALLOW_DATA_LOSS as a first go at recovery
@Scott – great questions. Sounds like some great ideas for a follow-up post!