Challenge 7 Details
On to the Week 7 Corruption Challenge. For this challenge, any version of SQL Server from 2005 or newer will work, however I don’t know on Azure, and I haven’t tried it on SQL Server 2016 CTP2 yet. I did test it on SQL Server 2005 and on SQL Server 2014.
Not all missing data is caused by hardware failures or issues, sometimes it is caused by people. – Unknown DBA
Here are the challenge details for this week.
During the following steps work was being performed on the database.
- 5:50PM a full backup was performed.
- 5:51PM transaction log backup.
- 5:52PM transaction log backup.
- 5:53PM transaction log backup.
- 5:54PM transaction log backup.
- 5:55PM transaction log backup.
- 5:56PM transaction log backup.
- Around 5:57PM to 5:58pm data from the [OPEN_NFIRS].[Record1000] users reported the table was no longer able to be queried, red error messages when selecting from [OPEN_NFIRS].[Record1000]. Soon thereafter that the data in the table was missing. Someone noticed that the table was empty, then turned off transaction log backups, and put the database in single user mode to prevent any more changes.
- Around 5:59PM the database was detached, and the MDF, LDF, and NDF files were copied off.
Your goal if you choose to accept it is to get restore all the data in the [OPEN_NFIRS].[Record1000] table prior to its disappearance. Note: there may have been multiple DBA’s working on this system in several different time zones across the world.
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.
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];
The competition begins at 6:00am (Pacific time) on Tuesday June 2nd. The competition ends on Saturday June 6th at 11:59pm (Pacific time).
An additional clue was added at 6:50pm on 6/4/2015 if you need it. This was 61 hours after the challenge started.
After Week 6, I have decided to modify the scoring going forward, additional details added in bold.
- 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.
- 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:
The view the current score leader board, visit the Current Scores page.
This week there is a bonus point that can be earned at any point before the competition ends. If once you have the correct solution, you can determine what happened to all the data in the [OPEN_NFIRS].[Record1000] table, and who did it, you will then earn one extra point for the week.
The zip file contains a single full backup, several transaction log files, MDF, LDF, and NDF files.
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 shirts to at least 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.
One more note…
I am incredibly busy this week. I will get to all your emails regarding the challenge, but I won’t be able to respond much during normal working hours, I am onsite with working with a client most of this week. Please be patient with me, all results will be scored in the order that they were received.
This week the first winning solution that included requirements above was from Raul Gonzalez. Great work Raul !!! The solution was submitted just 58 minutes after the challenge began.
Winner #1 Raul Gonzalez
- #2 Pasquale Ceglie
- #3 Andrei Stefan
- Ivan Rodriguez Camejo
- Dave Patterson
- Patrick Flynn
- Subhro Saha
- Neil Abrahams
- André Kamman
- Lucas Kartawidjaja
- Jon Gurgul
- Rob Farley
- Prageet Surheley
- Andrew Pruski
- Michael J. Swart
- Herman Atmadja
- Kumar Muppa
- Michael Docherty
- Arthur Baan
- Bogdan Sahlean
- Current Scores
- Newsletter Sign up
- Database Corruption Challenge T-Shirt
- Week 1, Week 2, Week 3, Week 4, Week 5, Week 6, Week 7
- Slow DBCC CheckDB
- Database Corruption Worksheet