Most of the DBCC commands return their results as textual output, even if you have SSMS configured to return result sets to a grid. This makes examining the output a manual process that is prone to errors. It sure would be nice if there was a way to return the output to a grid.

If we were to examine Books Online (BOL) for DBCC in 2000 (http://technet.microsoft.com/en-us/library/aa258281%28v=sql.80%29.aspx) and 2005 (http://msdn.microsoft.com/en-us/library/ms188796%28v=sql.90%29.aspx), we would notice a section titled “Using DBCC Result Set Output”, with the phrase: “Many DBCC commands can produce output in tabular form by using the WITH TABLERESULTS option. This information can be loaded into a table for additional use.” This section has been removed from more recent versions, most likely because the results returned are not documented for the individual DBCC commands and are thus subject to change without notice.

Okay, let’s try this out. At the above BOL links, there are two DBCC commands that are documented to use the TABLERESULTS option: OPENTRAN and SHOWCONTIG. Testing all of the other DBCC commands shows that this option can also be used on the CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE commands. I’m going to continue this post with using DBCC CHECKDB. If you check BOL, it does not mention the TABLERESULTS option. Let’s first see the results we get without the TABLERESULTS option by running:

DBCC CHECKDB (master);

For the consistency check that is performed on the master database, I end up with over 300 lines of output. The key lines in the output are:

…
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
…

And the final two lines:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'. 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(Notice that when we run CHECKDB against the master database, the hidden database mssqlsystemresource is also checked.)

If we were to modify the above statement to use the TABLERESULTS option:

DBCC CHECKDB (master) WITH TABLERESULTS;

we would actually get two result sets – one for the master database, and one for the hidden mssqlsystemresource database. Notice that the “DBCC execution completed…” line is not in either of the result sets – it is still displayed on the Messages tab.

Now, most people usually modify this command to suppress informational messages and to show all error messages (it defaults to “only” the first 200 error messages per object), so the command that is normally used would be:

DBCC CHECKDB (master) WITH ALL_ERRORMSGS, NO_INFOMSGS;

And, hopefully, this only returns the message “Command completed successfully”. Let’s modify this command to use the TABLERESULTS option:

DBCC CHECKDB (master) WITH ALL_ERRORMSGS, NO_INFOMSGS, TABLERESULTS;

If there is no corruption in the database, it still returns only the message “Command completed successfully”. However, if there is corruption, you will get a result set back. So, I’m now going to run this against a database (Lab) where I have engineered some corruption. First off, let’s run CHECKDB without the TABLERESULTS option to check the initial output:

DBCC CHECKDB (Lab) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 885578193, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type In-row data), page (1:328). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 885578193, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type In-row data): Page (1:328) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'Tally' (object ID 885578193).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Lab'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Lab).

Here we can see that we indeed have corruption. Quickly now… is any of this corruption in a non-clustered index?

Running this statement with the TABLERESULTS option, we get a grid of the results:

DBCC CHECKDB (Lab) WITH ALL_ERRORMSGS, NO_INFOMSGS, TABLERESULTS;

(This screen shot is only showing some of the columns. Note that the message of the previous output for the minimum repair level is still on the Messages tab.)

As you can see, this already makes examining your errors somewhat easier. For instance, you can easily scroll through this output to see if the corruption is in non-clustered indexes (IndexId > 1), where the corruption could easily be fixed by scripting out the index definition, dropping and then re-creating the non-clustered index. Suppose you had over 100 errors… you can see how much faster this would be.

If you recall, the BOL description says that this data can be loaded into a table for further processing. Furthermore, this post is about persisting DBCC output data, which implies storing it into a table. So, let’s make a table, put these results into the table, and then run a query against it. First off, let’s make a local temporary table to hold the results (you could put this into a permanent table if you so desire):

IF OBJECT_ID('tempdb.dbo.#DBCCCHECKDB') IS NOT NULL
   DROP TABLE #DBCCCHECKDB;
CREATE TABLE #DBCCCHECKDB (
   Error       INTEGER,
   [Level]     INTEGER,
   [State]     INTEGER,
   MessageText VARCHAR(MAX),
   RepairLevel VARCHAR(MAX),
   [Status]   INTEGER,
   [DbId]     INTEGER,
   DbFragId   INTEGER,
   ObjectId   INTEGER,
   IndexId     INTEGER,
   PartitionId BIGINT,
   AllocUnitId BIGINT,
   RidDbId     INTEGER,
   RidPruId   INTEGER,
   [File]     INTEGER,
   [Page]     INTEGER,
   Slot       INTEGER,
   RefDbId     INTEGER,
   RefPruId   INTEGER,
   RefFile     INTEGER,
   RefPage     INTEGER,
   RefSlot     INTEGER,
   Allocation INTEGER);

Since the output from using TABLERESULTS isn’t documented, I’ve had to make some assumptions about the data types for these columns. To actually insert the output, we need to use the INSERT INTO … EXECUTE statement:

INSERT INTO #DBCCCHECKDB
EXECUTE ('DBCC CHECKDB (Lab) WITH ALL_ERRORMSGS, NO_INFOMSGS, TABLERESULTS;');

Now that we have the data stored in a temporary table, let’s run a query against the table to return some aggregated data.

— Get the objects, # of errors, and repair level

SELECT [DbId],
       database_name = DB_NAME([DbId]),
       ObjectId,
       [Object] = OBJECT_SCHEMA_NAME(ObjectId, [DbId])
                   + '.' +
                   OBJECT_NAME(ObjectId, [DbId]),
       ErrorQty = COUNT(*),
       RepairLvl = MAX(RepairLevel)
FROM   #DBCCCHECKDB
WHERE   Error NOT IN (8989,8990)
GROUP BY [DbId], ObjectId;

With which I get the following results:

So, there we go. Easy-peasy. By utilizing the TABLERESULTS option, the output of the DBCC CHECKDB command has been persisted into a table, and we are now able to run our own queries against that data. In the event that there is corruption in multiple indexes in a table, this query could easily be extended to get the number of errors in each index.

This post is re-published from my original post on SQL Solutions Group.

The post Persisting DBCC output data appeared first on Wayne Sheffield.