Week 8 Database Corruption Challenge

Download PDF

Challenge 8 Details

 

Week8

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.

Good luck.

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];

 

Mail your results to steveEmailbefore Wed June 24th, 2015 at 7:00pm pacific time. Results will be posted soon after that.

 

Competition Dates

The competition begins at 6:00am (Pacific time) on Friday June 19th. The competition ends on Wed June 24th at 7:00pm (Pacific time).

Additional Clues

An additional clue was added at 6:46am (pacific time) on June 23rd, 2015. Here are the details on the extra clue.

Updated Scoring

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 LinkedIn option was added when some people commented on the fact that they don’t have a blog, and the “Publish a Post” feature on LinkedIn is similar to a blog post.

The view the current score leader board, visit the Current Scores page.

 

Bonus Point

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.

File Download

The zip file contains a single full backup.

CorruptionChallenge8.zip

 

Winners:

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.

CorruptionChallengeShirt

 

 

Good luck!  Enjoy the challenge.

 

Related Links

Database Corruption Challenge Sponsored by

Stedman Solutions for Help with Database Corruption and Performance Tuning

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.