This challenge is more diabolical than previous weeks, at least it was more difficult to build than I had planned for. If I wasn’t out of work this week, I wouldn’t have had time to build this challenge (that’s a hint to anyone out there who could use some help from a remote DBA like me). Starting my business has just been a bit slower than I expected, but that’s alright, I will get there.
Week 1 was just a wild idea that I had to start this corruption challenge, and I quickly threw something together. Nobody knew it was coming, and it was about 8 hours before anyone even noticed the challenge. Week 1 was won by Brent Ozar, and the solution involved recovering the missing data from covering non-clustered indexes, removing the corruption, and replacing the data from what was stored from the NC indexes. There were several other ways to solve it.
Week 2 built on week 1 utilizing a similar database, but with different problems. Week 2 was won by Rob Farley and involved pulling what was not corrupt out of the corrupt table, storing it off, then pulling the missing data from a backup, truncating the corrupt table, and then inserting the saved off data. The week 2 winning solution was provided just over 3 hours after the challenge was posted.
Between week 2 and 3, I came up with the details on how scoring will work, decided on 3 criteria, the first correct solution each week gets 2 points, each other correct solution that is submitted before the deadline gets 1 point, and during the entire challenge, you can earn 1 point my linking to the Corruption Challenge from your or blog.
Week 3 was won by Randolph West just 37 minutes after the challenge was posted. This was the first week that Randolph has competed in the contest. This solution involved using full backups, log backups, and pulling a tail of the log backup from a database where the mdf was missing and there was only a ldf file. This one was a lot of fun, however people were able to solve it quicker than I had expected.
Between week 3 and week 4, I designed a t-shirt that is available for purchase, and that will be awarded to the champion of the Corruption Challenge games at the end of the 10 week period.
On to week 4, it is difficult for me to judge how long this will take, as I was the one who corrupted the database, and I know exactly what is broken. I would like to say that even knowing these things, it took me longer to solve this challenge than any of the other week challenges.
Week 4 looks something like this.
To get started, check out the details here, and be sure you read all the details on what you need to submit to be considered a winner.
- Current Scores
- Newsletter Sign up
- Database Corruption Challenge T-Shirt
- Week 1, Week 2, Week 3
- Database Corruption Worksheet