Skip to content

Corruption

Database Corruption Challenge #10

Welcome to Week 10 of the Database Corruption Challenge. I have created a more corrupt, and perhaps more diabolical corruption scenario than previous weeks. This is the last week of 10 in the Database Corruption Challenge.

Here is how it works; I have created a corrupt database, then solved the corruption myself in order to prove that it is possible to fix, without data loss.

Database Corruption Challenge Details

There will be a total of 6 points available in this weeks challenge.

  • 1 point for being the first to solve the corruption challenge, and posting something about the corruption challenge on Twitter or LinkedIn. Please reference http://SteveStedman.com/Corruption in the post.
  • 1 point for having the solution right the first time you submit your solution.
  • 3 points for solving all the corruption with no data loss and providing the TSQL code (or other detailed steps) that was used to fix the corruption.
  • 1 point for completing the Blog Interview Questions. This does not have to be submitted with your solution, but it does need to be submitted by the end of the competition.

Read More »Database Corruption Challenge #10

Database Corruption Challenge #10 Grand Finale – Starts Friday

The Database Corruption Challenge – Grand FinaleCorruptionChallenge

The final challenge #10 in the Database Corruption Challenge starts this Friday (July 17th) at 6:00pm (Pacific Time).

After 9 other challenges we come to the final competition in this series. Many have competed, many have won, this is your chance to participate.

For those who have succeeded in all the other 9 Database Corruption Challenges you should not have a problem, however this will be the most complex corruption challenge yet.

Read More »Database Corruption Challenge #10 Grand Finale – Starts Friday

Database Corruption Challenge #9 – How I Corrupted the Database.

I was asked how I caused the corruption in Database Corruption Challenge #9. Here is how I did it.

To cause the corruption, I used the undocumented DBCC WritePage, however the same thing could have been accomplished by detaching the database, opening it with a hex editor, and then re-attaching the db.

DBCC WritePage

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.

Read More »Database Corruption Challenge #9 – How I Corrupted the Database.

Database Corruption Challenge #9 Complete

Database Corruption Challenge #9 has completed with 19 winning participants, you can take a look at the scoring page for exact details. The first to solve the challenge this week was Eduardo Rezende who provided the winning solution just 58 minutes after the challenge began. The second winning solution arrived less than a minute after the first, so it was a very close win for Eduardo. If you know Eduardo (or even if you don’t), take a moment to congratulate him on his win.

Thee was no additional clue provided this time, nobody asked for one and everyone who submitted a solution had the correct answer.

The corruption consisted of several corrupt pages in a nonClustered index, and two different tables with clustered indexes that had corruption. What was interesting about the corruption was that in the two clustered indexes, even though DBCC CheckDB or CheckTable show errors, it was still possible to use SELECT * FROM tablename and get all the results back. Which meant that the corruption could be fixed by selecting everything from the table, deleting the corrupt pages, and then putting the missing data back into the table. The challenge was that one of the tables had many other tables with foreign key constraints. Some solved it by dropping the foreign keys, truncating the table, then reinserting the data and recreating the foreign keys. Others solved it by using DBCC CheckTable with the REPAIR_ALLOW_DATA_LOSS option, then they just put the rows back that were missing.

Read More »Database Corruption Challenge #9 Complete

Database Corruption Challenge #9

Welcome to the ninth competition of ten in the Database Corruption Challenge. This week I have created a database and then corrupted one or more parts of that database, and your goal is to fix all of the corruption without losing any data. There is a total of 4 points available this week:

  • One point for being the first person to provide a correct solution with no data loss. Won by Eduardo Rezende.
  • One point to all who get a correct solution with no data loss.
  • One point extra if you get the correct answer before any hints are given.
  • One point extra if your solution is correct the first time you submit it to me. (check your work)

For information on scores take a look at the current scores page.

Challenge9

Corruption Challenge General Info

The challenge will be to download the corrupt or somehow damaged database and attempt to recover it. If you can recover it, please send me the steps you used to recover the database, along with some proof that the database has been recovered. The goal each week will be the following:

Read More »Database Corruption Challenge #9

Database Corruption Challenge #8 has Ended

With many participants and 16 who correctly solved Corruption Challenge week 8, the challenge has come to an end. The overall winner was Rob Farley, and there were several new participants this week. The scores page has been updated.

Some participants called this the toughest challenge yet which makes my job even harder to come up with something more challenging for next time.  There were a couple interesting twists in this one. One was the way the corruption caused issues with DBCC Page. On SQL Server 2014 DBCC Page on the corrupt page with the output parameter of 3 didn’t work, and the only output option that worked was option 2. However on SQL Server 2008R2 and 2012, none of the DBCC Page output options worked on the corrupt page, which forced some to open the database file with a hex editor to pull the data in from the corrupt page.

The one thing that I didn’t judge anyone badly with was the capitalization of the person in the corrupt record.  I accepted EMMA, E WILLIAMS, and Emma, E Williams, and Emma, E WILLIAMS all as correct answers, as to figure out any of those it took the same amount of work.

The solution by Rob Farley:

Read More »Database Corruption Challenge #8 has Ended

Corruption Challenge #8

Welcome to the eighth week of 10 in the Database Corruption Challenge (DBCC), this is an about weekly competition. Here is how it works; I have created a corrupt database, hopefully more corrupt or more interesting than the previous week. I then solved the corruption myself in order to prove that it is possible to fix, without data loss. There is a total of 4 points available to be earned this week.

  • 1 point extra for being the first correct answer. Already taken by Rob Farley.
  • 1 point to all who get the correct answer.
  • 1 point extra if you get the correct answer before any hints are given.
  • 1 point extra if your solution is correct the first time you submit it to me.  (check your work)

Week8

Corruption Challenge General Info

The challenge will be to download the corrupt or somehow damaged database and attempt to recover it. If you can recover it, please send me the steps you used to recover the database, along with some proof that the database has been recovered. The goal each week will be the following:

Read More »Corruption Challenge #8