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;
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;
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.
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.
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.
Getting Help from Steve and the Team
Contact us for your Free 30 minute consultation ORschedule your appointment now.