Week 4 Extra Clue

Download PDF

SPOILER ALERT….

Page down,

if you would like

to read the clue,

however if you

want to proceed

without the clue

stop reading here.

Page down,

if you would like

to read the clue,

however if you

want to proceed

without the clue

stop reading here.


 


 

Week 4 is proving to be more challenging than previous weeks.  So far 16 hours after the challenge was release, only 1 solution has been provided, and he spent over 2 hours working on it.

Keep in mind that I did state that it is possible to recover this table with no data loss, and one person has done that. And yes, this can be completed with no data loss. Also I would like to point out that the winning solution provided by Randolph West was slightly different than my solution, so there are indeed 2 ways to solve this.

Also, I would like to reiterate my previous statement of “100% of the data can be recovered without the dirty tricks of DBCC WritePage”. It is indeed true that this can be solved without using DBCC WritePage, it doesn’t however state that it can’t be solved using DBCC WritePage. I just think that using DBCC WritePage in this case would turn out to be much more tedious than you might think. I will still accept answers that use DBCC WritePage, there are just easier ways to do this.

Several people have stated that this is too diabolical and have asked for a clue.

Here is the first clue.

Several have discovered that multiple pages in the [Customers] table are corrupt, in fact many pages are corrupt. I don’t think there is a reasonable way to save the pages that currently exist in the [Customers] table.

I tried the following which failed with an interesting error message:

TRUNCATE TABLE Customers;

So I ended up doing the nasty REPAIR_ALLOW_DATA_LOSS, which left me with an interesting [Customers] table.

ALTER DATABASE [CorruptionChallenge4]
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckTable(Customers, REPAIR_ALLOW_DATA_LOSS);

Keep in mind that the above statement does cause data loss, but I did state that this can be done with no data loss, so the big question is if CheckTable destroys data, how do I get it back, how do I know what was destroyed?

End of clues…

Related Links

Database Corruption Challenge Sponsored by
StedmanSolutionsRemoteSkilledDBA

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.