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…

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

On to the winners.  Here is this weeks list of winners with the first one to submit a correct solution being Rob Farley

#1 Rob Farley

• #2 – Nicolette Carpenter Boddie
• #3 – Parikshit Savjani
• #4 – Ivan Rodriguez Camejo
• Subhro Saha
• Neil Abrahams
• Rui Bastos
• Lucas Kartawidjaja
• Piche Sebastien
• Bogdan Sahlean
• Raul Gonzalez
• Pasquale Ceglie
• David Patterson
• Patrick Flynn
• Arthur Baan
• André Kamman
• Prageet Surheley

The timing between #1, #2 and # 3 was very close. Solution #3 came in about 7 minutes after #1, and #3 was about 8 minutes later.

For those who haven’t signed up yet, I have created a newsletter where you can sign up to get notification ahead of time when the next corruption challenge will be started.

The winning solution

Rob Farley provided the winning solution, shown here. Note: SQL formatting has been changed to fit the blog post.

He first restored the databases to CorruptionChallenge2, and CorruptionChallenge2_OLD.

What I like about this solution was that all of the records were located and stored in another table Rev2a prior to anything being deleted, truncated, or cleaned up. The three queries shown here with the UNION ALL connecting them find the missing data. The first one finds all the data before the corrupt page. The second query finds everything after the corrupt page. The last part pulls the rows in from the backup database using the ncDeptIdYear index from the corrupt table to find the rows that need to be inserted.

SELECT *
  INTO dbo.Rev2a
  FROM (SELECT TOP 595 *
          FROM CorruptionChallenge2.dbo.Revenue
         ORDER BY id ASC) t1
UNION ALL
SELECT *
  FROM (SELECT TOP (9450) *
          FROM CorruptionChallenge2.dbo.Revenue
         ORDER BY id DESC) t2
UNION ALL
SELECT *
  FROM CorruptionChallenge2_OLD.dbo.Revenue
 WHERE id IN
  (
  SELECT id
    FROM CorruptionChallenge2.dbo.Revenue WITH (INDEX(ncDeptIdYear))
   WHERE id NOT IN
    (SELECT id FROM (SELECT TOP 595 *
    FROM CorruptionChallenge2.dbo.Revenue
    ORDER BY id ASC) t1
    UNION ALL
    SELECT id
	  FROM (SELECT TOP (9450) *
              FROM CorruptionChallenge2.dbo.Revenue
             ORDER BY id DESC) t2
    )
);

Once that query was run, you can see there were 10057 rows inserted into the table Rev2a which was then used to help fix things up. Next lets take a look at what is in Rev2a.

SELECT * FROM dbo.Rev2a;

Now here is how Rob took care of the corruption with TRUNCATE table on the Revenue table, then he refilled it with the right data.

TRUNCATE TABLE dbo.Revenue;
GO
SET IDENTITY_INSERT dbo.Revenue ON;
GO
INSERT dbo.Revenue (id, DepartmentID, Revenue, Year, Notes)
SELECT *
FROM dbo.Rev2a;
GO
SET IDENTITY_INSERT dbo.Revenue OFF;
GO

Next he cleaned up his Rev2a table that was used to hold the data.

DROP TABLE dbo.Rev2a;

Then he ran the check procedure to confirm that his solution was correct, which it was.

EXEC dbo.checkCorruptionChallenge2Result;

The stored procedure to check the solution was something new that I added, and there were a number of comments from people about it. I did give people a way of telling if they got things right, however it didn’t simulate a real world experience, as in the real world, there will be no magic stored procedure to run that tells you if you fixed the corruption.

That is how this weeks database corruption challenge was won.

Again, I would like to congratulate everyone who participated and either learned something new or had a chance to practice their skills.

Stay tuned for the next corruption challenge next week, and this week I will post a few of the other winning solutions.

-Steve Stedman

Related Links:

• Database Corruption Worksheet
• Corruption Challenge Week 1
• Corruption Challenge Week 2
• Stedman Solutions (my consulting business)

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!