Week 10 Database Corruption Challenge

Week 10 Database Corruption Challenge specific details

Timeline of events leading up to this database needing help:

Date and times on the files are for July 14th 2015.

At about 4:50pm you get an email from a developer reporting that when they query the orders table that it appears to have a lot more rows in it than they were expecting. They said that yesterday it had data, but not millions of rows. Now they cancelled their query after 3 minutes and 28 seconds and it had returned 23 million rows. “How could there be so many rows in that table?” they ask.

Shortly after that someone else noticed that the History table which previously had data in it was empty.

Download the Database Corruption Challenge

DatabaseWeek10.zip

This corruption challenge was created on SQL Server 2005, and tested on SQL Server 2008R2 and on SQL Server 2014. It should also work on SQL Server 2008 and SQL Server 2012. I didn’t et around to testing it on SQL Server 2016.

Good Luck!

Submitting Your Solution

When you think you have success, run the following queries and send me the results, along with the usual:

Detailed steps with what you did to complete the challenge (TSQL script preferable).
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 three queries:

SELECT COUNT(*) as OrdersCheck1,
       CHECKSUM_AGG(CHECKSUM([id])) as OrdersCheck2,
       CHECKSUM_AGG(CHECKSUM([orderDate])) as OrdersCheck3,
       CHECKSUM_AGG(CHECKSUM([customerId])) as OrdersCheck4,
       CHECKSUM_AGG(CHECKSUM([shippingType])) as OrdersCheck5,
       CHECKSUM_AGG(CHECKSUM([orderDetails])) as OrdersCheck6,
       CHECKSUM_AGG(CHECKSUM([totalPrice])) as OrdersCheck7
  FROM Orders;

SELECT COUNT(*) as HistoryCheck1,
       CHECKSUM_AGG(CHECKSUM([id])) as HistoryCheck2,
       CHECKSUM_AGG(CHECKSUM(ISNULL([event], ''))) as HistoryCheck3,
       CHECKSUM_AGG(CHECKSUM(ISNULL([when], ''))) as HistoryCheck4
  FROM History;

SELECT COUNT(*) as RevenueCheck1,
       CHECKSUM_AGG(CHECKSUM([id])) as RevenueCheck2,
       CHECKSUM_AGG(CHECKSUM(ISNULL([DepartmentID], ''))) as RevenueCheck3,
       CHECKSUM_AGG(CHECKSUM(ISNULL([Revenue], ''))) as RevenueCheck4,
       CHECKSUM_AGG(CHECKSUM(ISNULL([Year], ''))) as RevenueCheck5,
       CHECKSUM_AGG(CHECKSUM(ISNULL([Notes], ''))) as RevenueCheck6,
       CHECKSUM_AGG(CHECKSUM(ISNULL([Status], ''))) as RevenueCheck7
  FROM Revenue;

Mail your results to before Monday July 20th, 2015 at 4:00pm pacific time. Results will be posted soon after that.

Blog Interview Questions

To get the extra point and to be featured on my blog, please answer the following questions. You don’t have to submit these with your solution, you can submit the blog interview questions up until the end of the challenge.

All answers are subject to being posted on http://SteveStedman.com

What compelled you to want to participate in the Database Corruption Challenge?
What did you find the most interesting in the Database Corruption Challenge?
What did you learn in the Database Corruption Challenge?
How many weeks did you participate in the Database Corruption Challenge?
Any suggestions to those who might want to give some of the 10 Database Corruption Challenge examples a try?
What is your twitter handle, and your linked in url (if you have either)?

High Scoring Participants

The top 10 scoring participants in the challenge will each be awarded a Database Corruption Challenge t-shirt. So if you think you are going to be in the top 10 be sure to submit your t-shirt size with your entry this week.

For all those attending PASS Summit 2015 in Seattle, your shirt will be awarded there, if you are not attending PASS Summit, or would like your shirt sooner than that, I can ship it to you as long as you pay the shipping charges.

Winners

This week was won by Rob Farley who earned 6 points, followed a few minutes later by Randolph West.

#1 – Rob Farley (Australia)
#2 – Randolph West (Canada)
#3 – Gustavo Ayala (Argentina)
Ivan Rodriguez Camejo (US)
Raul Gonzalez (UK)
Sabin Bio (Romania)
Boris Bidal (France)
Neil Abrahams (UK)
Dan Andrei Stefan (Romania)
Lucas Kartawidjaja (US)
Patrick Flynn (Australia)
Andrew Pruski (Ireland)
André Kamman (Netherlands)

Good luck! Enjoy the challenge.

Week 10 Database Corruption Challenge