If you ask a senior DBA what are the top 5 most important commands in T-SQL you’ll probably see DBCC CHECKDB in most if not all of those lists. Unfortunately, the output can be long, overwhelming and a more than a bit difficult to read. So here are a few tips you can use to make the output significantly more readable.

To start with I’m borrowing a corrupt database from Steve Stedman (b/t). Specifically from week 6 of his corruption challenge.

I do want to stop here for just a sec and point out what the Corruption Challenge was. Steve corrupted 10 databases then challenged everyone to fix them. At the time there were prizes and bragging rights for the first to find the solution. But what about now? Well, if you are a DBA (the type that might actually have to deal with a corrupt database at some point) I HIGHLY recommend going through all 10 weeks. Try to solve them yourself and then read through all of the solutions. This is awesome practice and by reading the solutions you will see lots tricks and tips that will help you when you have to deal with a corrupt DB in real life.

The first thing I did was download the database and restore it. And another quick point here. Notice that there is a backup of the corrupt database? Before you try to fix a corrupt database, back it up. That way if you mess something up, you can at least get back to the current bad state.

Next, I ran

DBCC CHECKDB ('CorruptionChallenge6')

You can see that we had errors in the status bar.

But there are 197 total lines and I had to skim down to line 182 to find the error. And this is a small sample database. I’ve seen output with 1000s of lines. And not only could the first error be anywhere in there but there could be errors scattered all through it. How exactly do we find them?

Let’s start with NO_INFOMSGS.

DBCC CHECKDB ('CorruptionChallenge6') WITH NO_INFOMSGS

And now we have a totally readable 9 lines of output.

Msg 8938, Level 16, State 2, Line 6

Table error: Page (1:1849), Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Unexpected page type 1.

Msg 8976, Level 16, State 1, Line 6

Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1849) was not seen in the scan although its parent (1:1832) and previous (1:1848) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 6

Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1850) is missing a reference from previous page (1:1849). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 3 consistency errors in table ‘Customers’ (object ID 2073058421).

CHECKDB found 0 allocation errors and 3 consistency errors in database ‘CorruptionChallenge6’.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge6).

Not bad at all. But we can do better.

DBCC CHECKDB ('CorruptionChallenge6') WITH NO_INFOMSGS, TABLERESULTS

Now fair warning, TABLERESULTS is not documented for CHECKDB so all of the standard “Microsoft could change this without warning”, “be careful using this in production” etc. comments. It is documented for other DBCC commands, however, so it doesn’t seem unreasonable to use it, just be aware.

FYI You can also use TABLERESULTS without NO_INFOMSGS but it’s harder to see the errors for the non-errors because they aren’t red. I’m not 100% certain but from what I can tell the RepairLevel column only has a value for the error rows so it should still be possible to tell the difference.

But what about automation I hear you cry! Can we get this output in an automated process? Yes we can!

-- Create a table to store the output
CREATE TABLE DBCCCheckDBOutput (
[DBName] nvarchar(400),
[RunTime] datetime,
[Error] smallint,
[Level] tinyint,
[State] tinyint,
[MessageText] varchar(2000),
[RepairLevel] varchar(50),
[Status] tinyint,
[DbId] int,
[DbFragId] int,
[ObjectId] int,
[IndexId] int,
[PartitionId] bigint,
[AllocUnitId] bigint,
[RidDbId] int,
[RidPruId] int,
[File] int,
[Page] int,
[Slot] smallint,
[RefDBId] int,
[RefPruId] int,
[RefFile] int,
[RefPage] int,
[RefSlot] smallint,
[Allocation] int
)
GO

-- Run CHECKDB on all databases and store the output into DBCCCheckDBOutput
DECLARE dbList CURSOR
KEYSET
FOR SELECT name FROM sys.databases;
DECLARE @DBName nvarchar(400);
DECLARE @sql nvarchar(4000);
OPEN dbList;
FETCH NEXT FROM dbList INTO @DBName;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @sql = N'DBCC CHECKDB (' + quotename(@DBName) + 
N') WITH NO_INFOMSGS, TABLERESULTS';
INSERT INTO DBCCCheckDBOutput (
[Error], [Level], [State], [MessageText],
[RepairLevel], [Status], [DbId], [DbFragId],
[ObjectId], [IndexId], [PartitionId], [AllocUnitId],
[RidDbId], [RidPruId], [File], [Page], [Slot], [RefDBId],
[RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation])
EXEC sp_executesql @sql;
UPDATE DBCCCheckDBOutput 
SET DBName = DB.name,
RunTime = getdate()
FROM DBCCCheckDBOutput
JOIN sys.databases DB
ON DBCCCheckDBOutput.DbId = DB.database_id
WHERE DBCCCheckDBOutput.DBName IS NULL;
END
FETCH NEXT FROM dbList INTO @DBName;
END
CLOSE dbList;
DEALLOCATE dbList;
GO

All of that said, once it’s out I’d personally give Minion CheckDB a shot. Based on what I know of the other Minion products I’m sure there will be a ton of logging and other options.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: checkdb, corruption, microsoft sql server