Database Corruption Challenge Winners

Today I am proud to announce that the Database Corruption Challenge has come to an end. I would like to say that I have met some really great people during this contest and I look forward to seeing you at PASS Summit in October, or other conference someday.

The Corruption Challenge Winners

Grand Champion - Rob Farley After 10 competitions Rob Farley is the overall Champion of the Challenge with 25 points. Rob won week 2, Week 8 and Week 10. I will get the winning solution posted to my blog in the next day or two for those who are wondering how this weeks challenge was won.

Here is the list of the top scoring participants, the top 10 will receive a limited edition prize t-shirt as their badge of honor for being the top scoring participants:

CorruptionChallengeShirt

1^st Place with 25 points: Rob Farley
2^nd Place with 22 points: André Kamman
3^nd Place Tie with 21 points: Neil Abrahams, Patrick Flynn and Raul Gonzalez
6^th Place with 18 points: Ivan Rodriguez Camejo
7^th Place tie with 17 points: Dan Andrei Stefan and Lucas Kartawidjaja
9^th Place with 15 points: Pasquale Ceglie
10^th Place Tie with 12 points: Andrew Pruski, Randolph West, Subhro Saha

See the scores page for overall ranking for all participants with more then 1 point.

Note: rankings were determined with standard RANK rather than DENSE RANK.

Due to the three way tie for 10th place there are actually a total of 12 winners eligible to receive the prize of the Database Corruption Challenge t-shirt. If your name is listed above, and you haven’t sent me your t-shirt size, please send it to me soon so I can get the shirts ordered.

All 10 Competitions

There were only 3 people who competed in all 10 of the challenges, those were Rob Farley, Neil Abrahams and André Kamman. Nice work guys, and thanks for helping keep it interesting, I know that your participation in all 10 weeks took a lot of time and dedication.

History of the Database Corruption Challenge

Since the beginning of April I have run the Challenge, on what started out as a weekly event that evolved into more of an every other week event. There were many different types of corruption encountered over the 10 contests. The very first one was the quickest to create, however I quickly learned that I needed to support older versions of SQL Server in the competition, not everyone is on 2012 or newer. From that point I most of the challenges worked with SQL Server 2005 or newer.

Here is the list of the 10 challenges with links to find out more:

Week 1 of the Database Corruption Challenge was won by Brent Ozar, his solution is posted here. Also there is an alternate solution provided by Andre Kamman.
Week 2 of the Database Corruption Challenge was won by Rob Farley, his solution is posted. An alternate solution was provided by Bogdan Sahlean.
Week 3 of the Database Corruption Challenge was won by Randolph West, and his week 3 solution is posted. There was no alternate for week 3, everyone who solved week 3 did it the same way.
Week 4 of the Database Corruption Challenge was won by Randolph West, his week 4 solution is posted. An alternate solution using CTE’s was posted by Patrick Flynn.
Week 5 of the Database Corruption Challenge was won by Patrick Flynn, and his week 5 solution is posted. An alternate solutions is also posted that I created.
Week 6 of the Database Corruption Challenge was won by Raul Gonzalez.
Week 7 of the Database Corruption Challenge was won by Raul Gonzalez, with an alternate solution from Patrick Flynn.
Week 8 of the Database Corruption Challenge was won by Rob Farley.
Week 9 of the Database Corruption Challenge was won by Eduardo Rezende.
Week 10 of the Database Corruption Challenge was won by Rob Farley.

For the final week of the competition I tried to make it interesting. I went back and took a look at some of the things that were done in week 1, and other early weeks. You may have recognized some of the tables as being similar to earlier weeks. However the corruption was a bit more complex than those earlier weeks. One thing that I did was to corrupt the m_prevPage, and m_nextPage pointers on pages in the [Orders] table. I have a blog post discussing that titled SQL Server Tables – BTree or Linked List?

Every Minute Counts

In the last 3 challenges, the first correct answer was followed by a second correct answer by another participant in less than 10 minutes, one of the weeks the second correct answer was only about 50 seconds later, so every minute counts for those who won the competitions. Every minute counts to win the Corruption Challenge, and usually every minute counts when you are dealing with real corruption issues.

Blog Interviews

There was an option in week 10 for participants to include answers to the blog interview questions with their solution. I will be posting many of these bog interviews over the next week. Check back to get insight to why some of the winner participated in the contest.

Modifications Along The Way

Since I didn’t actually do much planning in the beginning I had to make some adjustments along the way. I tried posting the challenge at different times on different days in an attempt to make the release of the competition as fair as possible to all who were involved.

I quickly realized that the point system wasn’t entirely fair, so I added an additional point option for those who submit the solution and get it right on their first try. I don’t mind helping people, but I helped you figure it out you shouldn’t get as many points as those who figured it out on their own.

The checksum queries were added along the way to quickly check the contents of tables without having to go through every row of the database. This made the judging and scoring of the results much quicker for me.

Full backups, transaction log backups and MDF, LDF and NDF files. We had quite the variety of ways to transfer the corrupt databases to the participants. I tried to make it as quick and easy as possible to get to the point of starting to work on the corruption.

Lessons Learned in Solving Corruption

The following are a few general rules for those having to deal with database corruption that seem to help out time and time again.

Take a tail of the log backup. You may never need it, but if you do it is good to have.
Have a plan to undo your attempt if something goes wrong. This may be using backup files, or just putting your attempt to fix the problem into a transaction. However you do it if something doesn’t work the way you expected, be sure to have a way to undo it.
Consider all the files you have available. Even if your database is corrupt and the backup contains corruption, you might be able to save some data from the backup, and some from the running database.
DBCC WritePage… Only used this as a last resort when all other options have been exhausted. You may want to consider not ever running it on your production system, but rather an a test system or restored copy of the production database. Most of the time there are other options, consider the Week 7 Alternate solution from Patrick Flynn, which pulled data from corrupt pages and avoided the use of DBCC WritePage.
Double check everything. Even when it looks simple, before and after repairing corruption be sure you know how many rows a table should have it it. Compare the rows in the table to non clustered indexes. Check everything you possibly can so that you know when you are done.
Ask for help, sometimes just explaining your plan to someone else will help you figure out if it will work or not. Contact me if you need some help.

Lessons Learned in Creating the Corruption Challenge

As an organizer of the competition here is what I learned.

10 competitions over three and a half months is a long time. Many things can happen in that amount of time, plan ahead.
Be clear, test everything.
Always use spellcheck.
Have a solid way to confirm the results.
Don’t take things to personal… Every you do, someone is going to love or hate. Sometimes people vent or complain because they are frustrated. Stick with it, and don’t take things to personal.
Get to know the participants, the people participating in the competition are a great bunch of SQL Server experts. I look forward to meeting everyone who participated in person one day.
Figure out how to fit it into your schedule. You probably noticed that about half way through the competition I switched it up from weekly to about ever other week, that was due to my work schedule changing significantly.
Don’t give up.
As the organizer I learned far more than I ever expected to. Every single week I learned something new from someone who had a different approach to the solution that I did.

What’s Next

I have created a survey looking for some feedback from participants to see where we go next, and to learn from those who participated. It’s not too long, so please take a minute or two to provide some feedback. I would really appreciate it.

Please fill out the survey at Google Docs
Thanks everyone who participated, YOU made this competition possible. Without your participation this competition would not have been possible.

Announcing the Database Corruption Challenge Winners