Automating DBCC Page

Way back in 2006, Paul Randal documented DBCC PAGE on his Microsoft blog at https://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx. In his post, you will notice that in order to return the output from DBCC PAGE to the screen, you need to enable trace flag 3604 first. The above blog post shows a few examples of the results and utilizing them for further actions. Unfortunately, this method requires manual intervention to get the necessary data from the page in order to work with it further.

You know, it sure would be nice if the manual intervention could be removed and to completely automate the task that you are looking for. This blog post is going to show you how this can be done, and it will give a few examples of doing so.

In my last blog post, I introduced an optional setting to several DBCC commands: WITH TABLERESULTS. This returns the output in tabular format, in a manner that allows the output to be consumed. As it turns out, this optional setting also works with DBCC PAGE. You can see this by examining the database information page (page 9) of the master database:

Source code

DBCC PAGE ('master', 1, 9, 3) WITH TABLERESULTS;

This statement, which can be run without the trace flag, returns the data on the page in 4 columns: ParentObject, Object, Field and VALUE. As it turns out, this particular page of every database has a lot of interesting information on it. Let’s automate grabbing a specific piece of information out of this page:

Source code

IF OBJECT_ID('tempdb.dbo.#DBCCPAGE') IS NOT NULL DROP TABLE #DBCCPAGE;
CREATE TABLE #DBCCPAGE (
       ParentObject VARCHAR(255),
       [OBJECT]     VARCHAR(255),
       Field       VARCHAR(255),
       [VALUE]     VARCHAR(255));
INSERT INTO #DBCCPAGE
EXECUTE ('DBCC PAGE (''master'', 1, 9, 3) WITH TABLERESULTS;');
 
SELECT LastGoodDBCCDate = CONVERT(DATETIME, VALUE)
FROM   #DBCCPAGE
WHERE   Field = 'dbi_dbccLastKnownGood';

This wonderful snippet of code returns the last time a successful DBCC CHECKDB was run against the master database. Whoa… how sweet is that? When you discover a server where the CHECKDB job has been failing to the point that the job only has failed entries in the job history, you can now find out just how long it’s been since it was last run successfully.

The “trick” to making this work is to encapsulate the DBCC command as a string, and to call it with the EXECUTE () function. This is used as part of an INSERT INTO / EXECUTE statement, so that the results from DBCC PAGE are inserted into a table (in this case a temporary table is used, although a table variable or permanent table can also be used). There are three simple steps to this process:

1. Create a table (permanent / temporary) or table variable to hold the output.
2. Insert into this table the results of the DBCC PAGE statement by using INSERT INTO / EXECUTE.
3. Select the data that you are looking for from the table.

By utilizing a cursor, you can easily spin through all of the databases on your instance to get when they last had a successful DBCC CHECKDB run against them. The following utilizes sp_MSforeachdb (which itself uses a cursor to spin through all of the databases) to do just this:

Source code

IF OBJECT_ID('tempdb.dbo.#DBCCPAGE') IS NOT NULL DROP TABLE #DBCCPAGE;
IF OBJECT_ID('tempdb.dbo.#CheckDBDates') IS NOT NULL DROP TABLE #CheckDBDates;
CREATE TABLE #DBCCPAGE (
       ParentObject   VARCHAR(255),
       [OBJECT]       VARCHAR(255),
       Field           VARCHAR(255),
       [VALUE]         VARCHAR(255));
CREATE TABLE #CheckDBDates (
       database_name   sysname,
       LastCheckDB     DATETIME);
 
EXECUTE sp_MSforeachdb '
TRUNCATE TABLE #DBCCPAGE;
INSERT INTO #DBCCPAGE
   EXECUTE (''DBCC PAGE (''''?'''', 1, 9, 3) WITH TABLERESULTS;'');
INSERT INTO #CheckDBDates
SELECT ''?'', CONVERT(DATETIME, VALUE)
FROM   #DBCCPAGE
WHERE   Field = ''dbi_dbccLastKnownGood'';';
 
SELECT database_name,
       LastCheckDB
FROM   #CheckDBDates;

And here we have every database on your instance, and the date that DBCC CHECKDB was last run successfully against it – well, at least for the databases that sp_MSforeachdb didn’t skip (see https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ for more information about this).

In summary, by utilizing the “WITH TABLERESULTS” option of DBCC, we can automate processes that use DBCC PAGE, instead of needing manual intervention to work your way through this.

In the next few installments, we’ll look at other uses of using “WITH TABLERESULTS” to automate DBCC output.