Week 8 – Extra Clue

Download PDF

SPOILER ALERT

Stop

reading

now

if

you

don’t

need

the

extra

clue.

Page down if you need the clue….

SPOILER ALERT

Stop

reading

now

if

you

don’t

need

the

extra

clue.

Page down if you need the clue….


The extra clue for week 8 is how to get one missing row back in this solution.

Most of the rows int the [Record1035] table are easy to get back, and the corruption in the non-indexes can be fixed by dropping and re-adding the indexes. The hard part is getting one row back.

By saving off all of the good data in the [Record1035] table then comparing it to the non-clustered index for that table (before fixing the index) you can see that there is one row missing with the [Record1035Id] of -9223372036854775435. Running the following query will show a coupe things.


DBCC CheckTable('OPEN_NFIRS.Record1035') WITH NO_INFOMSGS;

First we will skip all the error messages on Index Id 2 because those can be fixed by dropping and recreating the index. So lets look at the error messages in index Id 1 which is the clustered index.

Table error: Object ID 149575571, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594044743680 (type In-row data), page (1:249), row 27. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 26984 and 71.
Msg 8944, Level 16, State 12, Line 193
Table error: Object ID 149575571, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594044743680 (type In-row data), page (1:249), row 27. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 26984 and 71.
Msg 8928, Level 16, State 1, Line 193

From the above error messages we can see the corruption is in page 1:249 on row 27.

So we try to use DBCC page with the output parameter of 3 to find out what is on row 27.


DBCC TRACEON(3604) with no_infomsgs;
DBCC Page(CorruptionChallenge8, 1, 249, 3) WITH NO_INFOMSGS;

Week8_clue_details1

 

Where we find out that DBCC Page with the output option of 3 is broken. DBCC Page wont’ show the rows because of where the corruption is, so we take a look at DBCC Page with the output option of 2 which means to display the whole page.

It turns out that on SQL Server 2008R2 and SQL Server 2012 DBCC Page with the format parameter of 2 doesn’t return any results. You can detach the database, use a Hex editor and find the same information, it is a bit more work, but completely doable.


DBCC TRACEON(3604) with no_infomsgs;
DBCC Page(CorruptionChallenge8, 1, 249, 2) WITH NO_INFOMSGS;

Week8_clue_details2

 

Which isn’t entirely useful since we want to find out what is at row (or slot) 27. So we page to the very end of the DBCC Output to see the OFFSET TABLE. From there page up to row 27.

Week8_clue_details3

We can see that row 27 is located at offset 0x16e. So we page up in the output to find offset 0x16e from the top of the page.

 

From here we can see that the person in question is Emma, E WILLIAMS Chief. You can also easily pull out the the Authority date of 20141030 which shows up right after the word Chief. Before the name Emma we can also see 10730 which is the personnel id.

Week8_clue_details4

So we start building the INSERT Statement to use later.


INSERT INTO [dbo].[Record1035]
 ([Record1035Id]
 ,[Record1000Id]
 ,[AuthorityPersonnelID]
 ,[AuthorityFirstName]
 ,[AuthorityMiddleInitial]
 ,[AuthorityLastName]
 ,[AuthorityRank]
 ,[AuthorityAssignment]
 ,[AuthorityDate])

VALUES
(
??,
??,
'10730',
'Emma',
'E',
'Williams',
'Chief',
??,
'20141030',
)

The question marks represent what we don’t know yet.

Since this is just a clue you will need to figure out the rest from here.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.