Database Corruption Challenge #9 Complete

Database Corruption Challenge #9 has completed with 19 winning participants, you can take a look at the scoring page for exact details. The first to solve the challenge this week was Eduardo Rezende who provided the winning solution just 58 minutes after the challenge began. The second winning solution arrived less than a minute after the first, so it was a very close win for Eduardo. If you know Eduardo (or even if you don’t), take a moment to congratulate him on his win.

Thee was no additional clue provided this time, nobody asked for one and everyone who submitted a solution had the correct answer.

The corruption consisted of several corrupt pages in a nonClustered index, and two different tables with clustered indexes that had corruption. What was interesting about the corruption was that in the two clustered indexes, even though DBCC CheckDB or CheckTable show errors, it was still possible to use SELECT * FROM tablename and get all the results back. Which meant that the corruption could be fixed by selecting everything from the table, deleting the corrupt pages, and then putting the missing data back into the table. The challenge was that one of the tables had many other tables with foreign key constraints. Some solved it by dropping the foreign keys, truncating the table, then reinserting the data and recreating the foreign keys. Others solved it by using DBCC CheckTable with the REPAIR_ALLOW_DATA_LOSS option, then they just put the rows back that were missing.

DBCC CHECKTABLE('OPEN_NFIRS.Record1000', REPAIR_ALLOW_DATA_LOSS);

The corruption in the nonClustered index could be fixed by just dropping and recreating the NC index.

</pre>
DROP INDEX [Record1100_ids_plus_age] ON [OPEN_NFIRS].[Record1100]
GO

CREATE NONCLUSTERED INDEX [Record1100_ids_plus_age] ON [OPEN_NFIRS].[Record1100]
(
[Record1100Id] ASC,
[Record1000Id] ASC,
[AgeOfPerson] ASC
);
<pre>

There were many different solutions provided, some used DBCC Write Page (dangerous) to fix the problem while others rebuilt indexes, but in the end there were 19 winning participants this week.

• #1 – Eduardo Rezende
• #2 – Dan Andrei Stefan
• #3 – David Patterson
• Raul Gonzalez
• Gustavo Ayala
• Patrick Flynn
• Roger Hullstrung
• Neil Abrahams
• Sabin Bio
• Ivan Rodriguez Camejo
• Justin Manning
• Rob Farley
• Subhro Saha
• André Kamman
• Andrew Pruski
• Pasquale Ceglie
• Michael Docherty
• Kumar Muppa
• Lucas Kartawidjaja

So congratulations to everyone who participated, and stick around for the next corruption Challenge. The next challenge will start some time next week, those who are subscribed by my newsletter will get advance notification of the start date of the final corruption challenge in this series.

Related Links

• Current Scores
• Newsletter Sign up
• Database Corruption Challenge T-Shirt
• Week 1, Week 2, Week 3, Week 4, Week 5, Week 6, Week 7, Week 8, Week 9
• Slow DBCC CheckDB
• Database Corruption Worksheet

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!