Challenge 8 Details
You have been assigned to work on a database for another division of your company. It appears that nobody has known about this SQL Server for some time. It may have been corrupt for many weeks or months.
You run DBCC CheckDB to discover corruption. There are no backups, other than the backups provided in this challenge.
Submitting Your Solution
When you think you have success, run the following query and send me the results, along with the usual:
- Detailed steps with what you did to complete the challenge.
- Proof that you removed the corruption or to restore the missing data, and that you didn’t lose any data.
- Optional: Include your nationality when you submit your solution. One of the participants suggested that we include this on the scoring page.
- The output from the following two queries:
SELECT COUNT([Record1000Id]) as check1 ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2 ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3 ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4 ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5 ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6 ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7 ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8 ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9 FROM [OPEN_NFIRS].[Record1000]; SELECT COUNT([Record1035Id]) as check0 ,CHECKSUM_AGG(CHECKSUM([Record1035Id])) as check1 ,CHECKSUM_AGG(CHECKSUM([Record1000Id])) as check2 ,CHECKSUM_AGG(CHECKSUM([AuthorityPersonnelID])) as check3 ,CHECKSUM_AGG(CHECKSUM([AuthorityFirstName])) as check4 ,CHECKSUM_AGG(CHECKSUM([AuthorityMiddleInitial])) as check5 ,CHECKSUM_AGG(CHECKSUM([AuthorityLastName])) as check6 ,CHECKSUM_AGG(CHECKSUM([AuthorityRank])) as check7 ,CHECKSUM_AGG(CHECKSUM([AuthorityAssignment])) as check8 ,CHECKSUM_AGG(CHECKSUM([AuthorityDate])) as check9 FROM [CorruptionChallenge8].[OPEN_NFIRS].[Record1035];
The competition begins at 6:00am (Pacific time) on Friday June 19th. The competition ends on Wed June 24th at 7:00pm (Pacific time).
An additional clue was added at 6:46am (pacific time) on June 23rd, 2015. Here are the details on the extra clue.
After Week 6, the scoring was updated.
Each week you can earn the following points:
- 2 points for being the first to submit a correct solution, as long as you mention the contest on twitter, with a link to http://SteveStedman.com/Corruption.
- 1 point for submitting a correct solution before the deadline.
- Bonus: 1 extra point to everyone who completes the challenge before any extra clues are provided. I will wait at least 24 hours before posting any extra clues.
- There may be additional points described an a challenge description.
Once in the competition you can earn points for the following:
- 1 point for creating a blog post or LinkedIn post that talks about the challenge, and links to http://SteveStedman.com/Corruption (if you do this you should email me a link to the post). For the LinkedIn Post, status updates don’t count, you need to click the “Publish a Post” link.
The view the current score leader board, visit the Current Scores page.
There is a bonus point this week for those who get the solution right the first time they submit to me. Check your answers before submitting. I am happy to help you out, you just don’t get the bonus point if you need the additional help.
The zip file contains a single full backup.
The winner this week just 4 hours after the competition started is Rob Farley. He was able to eliminate the corruption and recover all the data.
- #1 – Rob Farley (Australia)
- #2 – Raul Gonzalez (UK)
- #3 – Patrick Flynn (Australia)
- Neil Abrahams (UK)
- Gustavo Ayala (Argentina)
- Dan Andrei Stefan (Romania)
- Michael Docherty (UK)
- Boris Bidal (France)
- André Kamman (Netherlands)
- Ivan Rodriguez Camejo (US)
- Sabin Bio (Romania)
- Pasquale Ceglie (Italy)
- Michael J. Swart (Canada)
- Eduardo Rezende (Brazil)
- David Patterson (US)
- Kumar Muppa (Australia)
And Finally… What about the t-shirts
A while back I announced that there would be T-Shirts awarded as a prize, then number of shirts would depend on the number of participants. As we get closer I am finalizing the number of shirts that will be awarded. Right now I am thinking that I will award at least 10 shirts to the top 10 scoring participants. I will hand these out to the winners at PASS Summit in October. For those who won’t be attending PASS Summit, I can ship the shirt to you, however there may be additional shipping charges depending on where you live.
Good luck! Enjoy the challenge.
- 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, Week 10
- Slow DBCC CheckDB
- Database Corruption Worksheet