Database Corruption Challenge Week 4 Results

It is my pleasure to announce the winners in the Week 4 Database Corruption Challenge. But first how about a rundown of challenge.

In order to make up for the difficult time that Challenge #3 was issued, Challenge #4 was issued at 2:00pm pacific time on a Friday afternoon. Those who had signed up for my Newsletter received early notification of when the corruption challenge would occur, but not details on what the challenge would be. When I launched it, I ran into a couple technical details, it appears that my WordPress site that hosts this blog was having difficulty with attachments larger that 8mb. So I ended up having to upload the file to another site where Database Health Monitor is hosted where the larger file was accepted. This delayed the start of the challenge about 20 minutes.

After the challenge released, it was quiet, almost to quiet. Then about 40 minutes into it, Randolph West messaged me on twitter to say that he was on it, and that he had discovered the CDC (Change Data Capture) tables. He also mentioned that he had dinner plans. He kept working on it, and at 4:55pm (pacific time) he provided me with proof that he had completed the challenge, however it wasn’t until 5:06pm (pacific time), that he provided me the full script showing his solution. At that point Randolph was the winner of the Week 4 Database Corruption Challenge. Two hours and 46 minutes after the challenge was able to be downloaded, Randolph had won the competition.

Here is a series of comments on Twitter from Randolph that I think sum up what many people felt about the challenge.

The “Computer says no” error message was caused by one of two database triggers that I added to make things more challenging, and to prevent people from being able to create or drop tables on the database. They looked something like this.

CREATE TRIGGER noNewTables
ON DATABASE for CREATE_TABLE
AS
BEGIN
	PRINT 'Hey that is forbidden. Not allowed. Computer says no.';
	ROLLBACK;
END
GO

CREATE TRIGGER noDropTables
ON DATABASE for DROP_TABLE
AS
BEGIN
	PRINT 'Hey that is forbidden. Not allowed. Computer says no.';
	ROLLBACK;
END
GO

For those who recognized the obscure reference from Little Britain (tv show) to Computer Says No… I hope it made you smile.

The Extra Clue

On Saturday morning, 17 hours after the challenge had begun, there were several emails in my inbox from people asking for a hint. I ended up posting the extra clue, which actually had more than one clue for those who read between the lines.

Clue 1:

The clue included the following:

I tried the following which failed with an interesting error message:
TRUNCATE TABLE Customers;
Msg 4711, Level 16, State 1, Line 2
Cannot truncate table ‘dbo.Customers’ because it is published for replication or enabled for Change Data Capture.

What made that error message interesting was that it was leading you in the direction of Change Data Capture.

Now hopefully you found my comment about the error message being interesting to actually be interesting, and perhaps you tried truncating the Customers table yourself. If you had, you would have seen the following:

Clue 2:

Then if you read the more obvious clue, you may have figured out that you could get the data back even after using DBCC CheckTable(Customers, REPAIR_ALLOW_DATA_LOSS);.

Clue 3:

In the list of related links at the bottom of the Extra Clue page, I included a link to my recent post on Change Data Capture.

The Winner

Randolph was able to complete the challenge as soon as it was released, and is indeed the champion this week. Congratulations Randolph West. His solution is posted here.

Other Winners

Through the rest of the weekend there were many correct answers submitted, several ended up being very similar to what Randolph West did utilizing the CDC tables, however was one that actually walked through every page in the Customers table and fixed the corruption one page at a time. Another one dumped the contents of every page into a temp table, and then using a CTE scanned through the results and pulled out all of the data from every bad page in the Customers table. Check back tomorrow for details on the CTE scanning database pages example.

Scoring

To see the latest standings, take a look at the scoring page.