Checking When DBCC CheckDB Last Checked Your DB

 I had a customer ask how they could use Foglight to find out when "dbcc checkdb" last ran against their SQL Server databases.

This is a great use case for User-Defined Collections!

As a refresher, my colleague Trent Mera wrote a great blog on this topic called User-Defined Collections (UDC). I did a post on creating a rule on the result of a user-defined collection. 

The difficult part is typically researching the problem, and either writing or finding something suitable to use to query the DB. Luckily there's this new thing called Google. My colleague Jason Hall sent me a script that he modified from this excellent post on the topic.

Here is the script that I used for a UDC in Foglight:

IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL
  DROP TABLE #temp;

CREATE TABLE #temp (
       Id INT IDENTITY(1,1),
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)

IF OBJECT_ID('tempdb.dbo.#DBCCres', 'U') IS NOT NULL
  DROP TABLE #DBCCres;

CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum INT
)

DECLARE
       @DBName SYSNAME,
       @SQL    varchar(512);

DECLARE dbccpage CURSOR
       LOCAL STATIC FORWARD_ONLY READ_ONLY
       FOR Select name
              from sys.databases
              where name not in ('tempdb')
              AND state_desc = 'ONLINE';

Open dbccpage;
Fetch Next From dbccpage into @DBName;
While @@Fetch_Status = 0

Begin
Set @SQL = 'Use [' + @DBName +'];' +char(10)+char(13)
Set @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' +char(10)+char(13)

INSERT INTO #temp
       Execute (@SQL);
Set @SQL = ''

INSERT INTO #DBCCRes
        ( DBName, dbccLastKnownGood,RowNum )
       SELECT @DBName, VALUE
                     , ROW_NUMBER() OVER (PARTITION BY Field ORDER BY Value) AS Rownum
              FROM #temp
              WHERE field = 'dbi_dbccLastKnownGood';

TRUNCATE TABLE #temp;

Fetch Next From dbccpage into @DBName;
End

Close dbccpage;

Deallocate dbccpage;

 
SELECT DBName,dbccLastKnownGood
       FROM #DBCCRes
       WHERE RowNum = 1;


DROP TABLE #temp
DROP TABLE #DBCCRes

I was skeptical that it would accept the temp table drops and creates, but it worked.

The last part of the T-SQL returns 2 columns that Foglight can use - DBName and dbccLastKnownGood:

SELECT DBName,dbccLastKnownGood
FROM #DBCCRes
WHERE RowNum = 1;

The key (pun intended) is to set the "Is Key" flag on the dbname column to true. 

Save the changes on the way out, and let the agent collect some data for 10 minutes or so.

On the User-Defined Collection drilldown, you should see data similar to this:

This is good so that we can review the last check date per database. If we want to write a rule to alert us that the last check was 'x' days ago, then it becomes a bit more difficult with this dataset. Writing rules on the data in a user-defined collection is "easy" when the query returns a single value. When there are multiple columns and/or rows, it's more difficult.

So.. you can modify the UDC query a little bit. I would keep everything "as-is", except replace the last SELECT with this:

select count(*) as check14
from #DBCCRes
where RowNum =1
and DATEDIFF(DD, dbccLastKnownGood,GETDATE()) > 14;

You can replace 14 with whatever number of days you're comfortable with. Now a single value gets returned to the UDC with the number of databases that had their dbcc checkdb done more than 14 days ago. You can refer to my post linked at the top on how to create a rule for this if you'd like.

To learn more and give Foglight a try, just go here for a trial download.