Easy to read CHECKDB9
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.
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: checkdb, corruption, microsoft sql server
9 thoughts on “Easy to read CHECKDB”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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?
It hasn’t been released yet to my knowledge. I’m hoping soon.
We JUST announced, it’s coming on Febuary 1. (I probably shouldn’t say this, but if you’re just dying, you can beg Sean for the beta.)
I did and got no reply… twice!!
On the other hand, I don’t see much point in log dbcc cehckdb I mean if I have corruption I want to be notified asap.
Off guess I would say the logging is going to be more about how long it took so that you can make predictions as to how much longer a given run will take.
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.
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.
Awesome! Certainly a process every shop should try to have.
Well, Minion CheckDB will be out on 2/1 and it does just that. So don’t put too much effort into your process cause I’ve made it really easy.