Skip to content

Current Scores

Patrick Flynn Interview – Database Corruption Challenge

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is Patrick Flynn.

Patrick placed in third with a 3 way tie for third overall in the the Database Corruption Challenge scoring 21 points.

Here are the overall statistics for Patrick in the Database Corruption Challenge

Read More »Patrick Flynn Interview – Database Corruption Challenge

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.

Read More »Database Corruption Challenge #9 Complete

Database Corruption Challenge #8 has Ended

With many participants and 16 who correctly solved Corruption Challenge week 8, the challenge has come to an end. The overall winner was Rob Farley, and there were several new participants this week. The scores page has been updated.

Some participants called this the toughest challenge yet which makes my job even harder to come up with something more challenging for next time.  There were a couple interesting twists in this one. One was the way the corruption caused issues with DBCC Page. On SQL Server 2014 DBCC Page on the corrupt page with the output parameter of 3 didn’t work, and the only output option that worked was option 2. However on SQL Server 2008R2 and 2012, none of the DBCC Page output options worked on the corrupt page, which forced some to open the database file with a hex editor to pull the data in from the corrupt page.

The one thing that I didn’t judge anyone badly with was the capitalization of the person in the corrupt record.  I accepted EMMA, E WILLIAMS, and Emma, E Williams, and Emma, E WILLIAMS all as correct answers, as to figure out any of those it took the same amount of work.

The solution by Rob Farley:

Read More »Database Corruption Challenge #8 has Ended

When Database Corruption Strikes

Today I am presenting to the session “When Database Corruption Strikes” to the High Availability and Disaster Recovery PASS Virtual Chapter.

Presentation

Here is the abstract:

You are working along month after month with no problems in your database. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error“, or something even scarier. Do you know what to do now? We will walk through 3 or 4 actual corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you should do to protect yourself when corruption strikes. Learn what to avoid that will make things worse. You will leave with a checklist of steps to take when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time

Read More »When Database Corruption Strikes