Skip to content

Database Corruption Challenge

Database Corruption Challenge T-Shirt

The Database Corruption Challenge (DBCC 2015) t-shirt is now available at CafePress. It looks something like this.

CorruptionChallengeShirt

Check it out and order one to show your participation in the Database Corruption Challenge.

Last week, I mentioned that I would come up with prizes for the contest, and that the prizes would be based on the number of participants each week. So here is what I have come up with:

Read More »Database Corruption Challenge T-Shirt

Database Corruption Challenge Week 3 Results

Challeng3_RecoveryPending

It is my pleasure to announce the winners in the Week 3 Database Corruption Challenge. But first how about a rundown of challenge.

The challenge was issued at 10:00pm (pacific time) on Friday night. Yes, this was a difficult time for those on the East coast of the US, and if you are from EST, next week I promise the time will be better for you. Trying to switch up the times to give everyone across the planet a fair advantage. Those who had signed up for my Newsletter received early notification of when the corruption challenge would occur, but not details on what the challenge would be.

Once the challenge was issued, I received a couple of partial submissions, one contained the correct steps, but no results. The second one contained the right results, but was missing the steps to do it. The first correct submission that contained the steps, and proof that it was done correctly came in 37 minutes after the contest had started.  It was almost an hour later before the next winning solution was sent in.

Read More »Database Corruption Challenge Week 3 Results

Database Corruption Challenge #2 – Alternate Solution

With week two of the Database Corruption Challenge being just as interesting as week one, and after seeing yesterdays blog posting showing the solution by Rob Farley, you might be thinking you are ready to take on corruption. If you are interested in giving the corruption challenge a try, please sign up for my newsletter so that you will be notified of the next corruption challenge.

Lets take a look at one of the more interesting solutions submitted by Bogdan Sahlean, who was the first to solve using DBCC Page to find the exact corrupt bytes, and DBCC WritePage to fix those corrupt bytes.

Now if you are reading this, and don’t know about DBCC WritePage, I caution you…  DBCC WritePage is not something you should just play with, and it is certainly not something that you should ever run on a production database. Consider yourself warned.  One of the dangers of using Write page that it’s use will make the DB unsupported if Microsoft support is required.

Read More »Database Corruption Challenge #2 – Alternate Solution

Database Corruption Challenge #2 Winners

It is my pleasure to announce this weeks winners in the Database Corruption Challenge – Week 2.

There were many great solutions submitted, and I hope that everyone learned something from this weeks challenge.

My favorite part of the whole challenge was this post on Twitter…

 

Foresight

 

The whole goal of the Database Corruption Challenge is to help everyone be better prepared for when they do encounter corruption.

Read More »Database Corruption Challenge #2 Winners

Corruption Challenge 1 – An alternative solution

After posting the winning solution for Corruption Challenge 1 from Brent Ozar, I realized that he and I both solved the corruption by using the REPAIR_ALLOW_DATA_LOSS option on CheckDb. A very nasty move, however it did repair the corruption.


DBCC CHECKDB ('',REPAIR_ALLOW_DATA_LOSS);

 

After reading some feedback, one of the winners stated:

As soon as he ran REPAIR_ALLOW_DATA_LOSS, I knew we weren’t on the same page. I just never do that unless I’ve exhausted all the other options.

Which is a good point, in this solution I was fairly certain as to what REPAIR_ALLOW_DATA_LOSS was going to do, however in a real world scenario, who knows what might be effected beyond the initial table that we know about.

There are several other options to clean up the corrupt table besides the REPAIR_ALLOW_DATA_LOSS option. These options still involve copying the data off to another table and finding the missing data from row 31, however how the corruption gets cleaned up varies widely with the following options:

Read More »Corruption Challenge 1 – An alternative solution

Corruption Challenge 1 – how I corrupted the database

Since the corruption challenge completed yesterday, I have had several request asking how I created the corrupt database. So here is the script that I used to create the Database Corruption Challenge 1.

First the initial setup. Most of this I stole from a query training session that I did several weeks ago. All I really needed was a table with some data in it.


CREATE DATABASE [CorruptionChallenge1];
GO

USE [CorruptionChallenge1];

CREATE TABLE Revenue
(
[id] INTEGER IDENTITY,
[DepartmentID] INTEGER,
[Revenue] INTEGER,
[Year] INTEGER,
[Notes] VARCHAR(300)
);

INSERT INTO Revenue ([DepartmentID], [Revenue], [Year])
VALUES (1,10030,1998),(2,20000,1998),(3,40000,1998),
 (1,20000,1999),(2,600400,1999),(3,500400,1999),
 (1,40050,2000),(2,400300,2000),(3,604000,2000),
 (1,30000,2001),(2,30000,2001),(3,703000,2001),
 (1,90000,2002),(2,200200,2002),(3,80000,2002),
 (1,10300,2003),(2,1000,2003), (3,900300,2003),
 (1,10000,2004),(2,10000,2004),(3,100300,2004),
 (1,208000,2005),(2,200200,2005),(3,203000,2005),
 (1,40000,2006),(2,30000,2006),(3,300300,2006),
 (1,709000,2007),(2,40000,2007),(3,400300,2007),
 (1,50000,2008),(2,50000,2008),(3,500300,2008),
 (1,20000,2009),(2,600030,2009),(3,600300,2009),
 (1,300700,2010),(2,70000,2010),(3,700300,2010),
 (1,80000,2011),(2,80000,2011),(3,800200,2011),
 (1,100030,2012),(2,90000,2012),(3,900300,2012),
 (1,10000,2013),(2,90000,2013),(3,900100,2013),
 (1,100400,2014),(2,900300,2014),(3,903000,2014),
 (1,102000,2015),(2,902000,2015),(3,902000,2015);

UPDATE Revenue SET [Notes] = CAST(NEWID() as VARCHAR(300)) + 'This is some varchar data just to fil out some pages... data pages are only 8k, therefore the more we fill up in each page, the more pages this table will flow into, thus simulating a larger table for the corruption example';

CREATE CLUSTERED INDEX [clustId] ON [dbo].[Revenue]
(
 [id] ASC
);

CREATE NONCLUSTERED INDEX [ncDeptIdYear] ON [dbo].[Revenue]
(
 [DepartmentID] ASC,
 [Revenue] ASC
);

CREATE NONCLUSTERED INDEX [ncBadNameForAnIndex] ON [dbo].[Revenue]
(
 [Year] ASC
)
INCLUDE ( [Notes]) ;

-- first lets look at the REVENUE table
SELECT *
 FROM Revenue;




Setup1


Read More »Corruption Challenge 1 – how I corrupted the database