May 2015

If you are looking for more info on the corrupt database with Week 6 of the Database Corruption Challenge, you can take a look at the original post.

Week 6 was won by Raul Gonzalez who submitted his winning solution just 45 minutes after the challenge began. Raul has solved 4 of the 6 challenges so far, and has scored 2 extra points for linking to the challenge, and commenting on the CheckDB post.

The solution this week was in the non-clustered index, and there was some corruption in the clustered index. If you were to just drop and recreate the non-clustered index, the corruption in the clustered index was not able to be found. Comparing the values in the non-clustered index to the clustered index show where the corruption exists.

Lets take a look at his solution. The only thing I changes was the directory paths on the restore to match my configuration.

USE master
GO
IF DB_ID('CorruptionChallenge6') IS NOT NULL BEGIN
	ALTER DATABASE CorruptionChallenge6 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE CorruptionChallenge6
END
GO

IF OBJECT_ID('tempdb..#fromIndex') IS NOT NULL	DROP TABLE #fromIndex
IF OBJECT_ID('tempdb..#fromTable') IS NOT NULL	DROP TABLE #fromTable
IF OBJECT_ID('tempdb..#goodData') IS NOT NULL	DROP TABLE #goodData

RESTORE FILELISTONLY
FROM DISK = 'C:\DBBackups\CorruptionChallenge6.bak'
GO

RESTORE DATABASE CorruptionChallenge6
FROM DISK = 'C:\DBBackups\CorruptionChallenge6.bak'
WITH NORECOVERY, REPLACE
	, MOVE 'CorruptionChallenge6' TO 'C:\SQL_DATA\CorruptionChallenge6.mdf'
	, MOVE 'CorruptionChallenge6_log' TO 'C:\SQL_DATA\CorruptionChallenge6_log.ldf'
GO

RESTORE DATABASE CorruptionChallenge6 WITH RECOVERY
GO

Read More »Week 6 – The Winning Solution – Database Corruption Challenge

There are many times that CheckDB ends up being extremely slow, sometimes taking more than a day to run. This can make life difficult if you are trying to find out what is corrupt.

DBCC CheckDB(MyBigDatabase) WITH NO_INFOMSGS;

There are several of the tricks that I use to speed up DBCC CheckDB, depending on the specific environment. What I am looking for is what others do when they need to run DBCC CheckDB on a big database that appears to take forever to complete?

Read More »Slow CheckDB – What do you do?