Skip to content

SteveStedman

Corruption Challenge 4 – Second Alternate Solution.

The following is a solution to Corruption Challenge #4 that uses DBCC WritePage to fix the corruption.  Before proceeding, be sure to read this warning.

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.

DBCC WritePage is a cool, but extremely risky way to solve this weeks Database Corruption Challenge, however, do NOT EVER RUN THIS ON A PRODUCTION SYSTEM.

Read More »Corruption Challenge 4 – Second Alternate Solution.

Database Health Monitor -Beta Version 10.1 Released Today

Today is the release of Database Health Monitor Beta 10.1. If you haven’t see the Database Health Monitor before, its a free application that I have been building over last 4 years.

It has been a while since the last release, so I thought I would just walk through and show a few of the features.

One thing that changed right off was in the previous version, these was a advertisement for my IndieGoGo campaign where I attempted to crowd fund the next phase of development on Database Health Reports. The IndieGoGo campaign has been removed.

Real Time Reports

The real time reports are a category of reports that query the database directly to find out something that is currently happening right now. See the Historic Reports below for the ability to find out what was happening at some point in the past.

One of the new reports added is the Suspect Paged report. If you have been participating the Database Corruption Challenge you will certainly understand the value of this report.

SuspectPages

Read More »Database Health Monitor -Beta Version 10.1 Released Today

Corruption Challenge 4 – Alternate Solution

Here is the solution provided by Patrick Flynn. Congratulations Patrick for being one of this weeks winners.

I really liked this solution for a number  of reasons, first it showed how to pull data from DBCC Page and actually convert it into rows and columns that could then be inserted back into a table. Secondly due to its use of Common Table Expressions.

 

This was done using SQL Server 2014.

Approach used was:

(a) Restore and check database – Corruption is related to Allocation Meta Data for dbo.Customers

(b) Use the non-clustered indexes on FirstName and LastName to recover data apart from Middle name – Total of 511740 rows in Table
Steps are in CorruptionChallange Setup.sql (requires disabling Database triggers

(c) Cannot directly select data from pages due to allocation issues but can select raw data vis DBCC Page (and DBCC IND)
Requires using DBCC Ind to get list of Data pages and DBCC Page to extract data
Steps to extract and Parse data are in DBCCPage_Extract

Read More »Corruption Challenge 4 – Alternate Solution

Corruption Challenge Week 4 – The Winning Solution

Congratulations to Randolph West who won the corruption challenge this week with the following solution which restored all of the data.

First he restored the database to get started. Note some of his code and comments have been reformatted to better fit in the blog format.

Restore database. I use KEEP_CDC and KEEP_REPLICATION because of the hints you dropped in your blog.
The hint is that there is 100% chance of data recovery, so I will look for Change Data Capture tables.

USE master;
GO
--DROP DATABASE [CorruptionChallenge4] ;
RESTORE DATABASE [CorruptionChallenge4]
   FROM DISK = N'C:\DBBackups\CorruptionChallenge4_Corrupt.bak'
   WITH FILE = 1,
        MOVE N'CorruptionChallenge4'
		  TO N'C:\SQL_DATA\CorruptionChallenge4.mdf',
        MOVE N'UserObjects'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf',
        MOVE N'CorruptionChallenge4_log'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
        NOUNLOAD,
    REPLACE,
    STATS = 5,
    KEEP_CDC,
    KEEP_REPLICATION;

Next he ran CheckDB to see what is wrong with this database.

Read More »Corruption Challenge Week 4 – The Winning Solution

Database Corruption Challenge Week 4 Results

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

In order to make up for the difficult time that Challenge #3 was issued, Challenge #4 was issued at 2:00pm pacific time on a Friday afternoon. 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. When I launched it, I ran into a couple technical details, it appears that my WordPress site that hosts this blog was having difficulty with attachments larger that 8mb. So I ended up having to upload the file to another site where Database Health Monitor is hosted where the larger file was accepted. This delayed the start of the challenge about 20 minutes.

After the challenge released, it was quiet, almost to quiet. Then about 40 minutes into it, Randolph West messaged me on twitter to say that he was on it, and that he had discovered the CDC (Change Data Capture) tables. He also mentioned that he had dinner plans. He kept working on it, and at 4:55pm (pacific time) he provided me with proof that he had completed the challenge, however it wasn’t until 5:06pm (pacific time), that he provided me the full script showing his solution. At that point Randolph was the winner of the Week 4 Database Corruption Challenge. Two hours and 46 minutes after the challenge was able to be downloaded, Randolph had won the competition.

Read More »Database Corruption Challenge Week 4 Results

Week 4 – Building the Corrupt Database

The following are the steps that I went through to create a more difficult Database Corruption Challenge for Week 4.
Here is how I built the database, nothing fancy, however I did add an extra FILEGROUP called [UserObjects].

CREATE DATABASE [CorruptionChallenge4]
 ON  PRIMARY
( NAME = N'CorruptionChallenge4',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4.mdf',
  SIZE = 4288KB ,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB ),
FILEGROUP [UserObjects]  DEFAULT
( NAME = N'UserObjects',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf' ,
  SIZE = 4096KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CorruptionChallenge4_log',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
  SIZE = 1088KB,
  MAXSIZE = 2048GB,
  FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

Notice, that to make things slightly more challenging, the database is a Case Sensitive Accent Sensitive database. I hope this didn’t cause too much trouble.
Next I created a couple of tables.

Read More »Week 4 – Building the Corrupt Database