Easy to read CHECKDB

9

November 28, 2016 by Kenneth Fisher

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.

checkdboutput

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

checkdboutput2

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.

9 thoughts on “Easy to read CHECKDB

  1. kast218 says:

    Kenneth, thanks for useful article. Cant find link to download Minion CheckDB on page http://minionware.net/checkdb/. Could you describe steps to get this solution?

  2. Yeah, there’s a lot of logging that takes place on all the different aspects of checkdb. It’ll help you with planning, history, etc. So a good example is when you run checktable instead of checkdb. If all you’re running is checktable, then the DB itself will never show that it’s had a checkdb op because it hasn’t. But with this type of logging you can see when all the equivalent ops have been run and you can consider that to be a checkdb. Also managing remote checkdb, custom snapshots, etc. The process of checkdb is more than the event itself.

    Even reporting isn’t as easy cause your reporting mechanism may be broken for a bit or you may simply want to report on any DBs that haven’t had a checkdb in a certain timeframe, etc. There’s lots of stuff revolving around this as a process.

    I haven’t sent out a new beta in a while so if you’ve put yourself on the list I’m about to send out RC1 and you should get it then if you’re on the list.

  3. Steve Hall says:

    Timely article, thank you. I’m about to look at creating a process to restore backups and check them, so I don’t get caught out by a corrupt database backup when I need one.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: