SQL Database Last Known Good DBCC CHECKDB

Database integrity check is one of the most important maintenance task in database. Most of the time, we either setup a maintenance plan or develop a custom script and scheduled it to run from SQL agent job. If we want to know if DBCC CHECKDB ran successfully, we can examine the Agent job history or log. But there is another (or better) way to look up when is the last time DBCC CHECKDB successfully ran.

The script below is intended for SQL Server 2005 and above. Run the script below to see when is the last successful integrity check with DBINFO command and its dbi_dbccLastKnownGood field. DBINFO is an undocumented DBCC procedure that return some interesting information about the database. If there is 1900-01-01 00:00:00.000 on the result, that means no integrity check has ever ran before for that particular database. Run the DBCC CHECKDB again and you should see the date updated if the integrity check run successfully.

DECLARE @DB nvarchar(max) = NULL;
--SET @DB = N'YourDatabase'; --NULL to list all databases

DECLARE @Command nvarchar(max);
DECLARE @ExecCommand nvarchar(max);

CREATE TABLE #DBInfoTemp
(
 ParentObject varchar(255)
 , [Object] varchar(255)
 , Field varchar(255)
 , [Value] varchar(255)
);

CREATE TABLE #LastCkTemp
(
 DatabaseName varchar(255)
 , LastKnownGoodDate varchar(255)
);

IF @DB IS NULL
BEGIN
 SET @Command = N'
  INSERT INTO #DBInfoTemp
  EXEC (''DBCC DBINFO([?]) WITH TABLERESULTS'');'
END
ELSE
BEGIN
 SET @Command = N'
  INSERT INTO #DBInfoTemp
  EXEC (''DBCC DBINFO([' + @DB + ']) WITH TABLERESULTS'');'
END

SET @ExecCommand = @Command + N'
 INSERT INTO #LastCkTemp
 SELECT 
  MAX(CASE WHEN di.Field = ''dbi_dbname''
   THEN di.Value
   ELSE NULL
   END) AS DatabaseName    
  , MAX(CASE WHEN di.Field = ''dbi_dbccLastKnownGood''
     THEN di.Value
     ELSE NULL
     END) AS LastCheckDBDate
 FROM #DBInfoTemp di
 WHERE 
  di.Field = ''dbi_dbccLastKnownGood''
  OR di.Field = ''dbi_dbname'';
  
 TRUNCATE TABLE #DBInfoTemp;
 ';

IF @DB IS NULL
BEGIN
 EXEC sp_MSforeachdb @ExecCommand;
END
ELSE
BEGIN
 EXEC (@ExecCommand);
END

SELECT
 ck.DatabaseName
 , ck.LastKnownGoodDate
FROM #LastCkTemp ck;

DROP TABLE #LastCkTemp, #DBInfoTemp;

You can also use DBCC PAGE to find out the last known good time stored in the boot page. DBCC TRACEON is to get the DBCC PAGE output. Why 9 for DBCC PAGE? Because Paul Randal say so! Page 9 is where it stores the last known good information. After you run the command, scroll to find dbi_dbccLastKnownGood

DBCC TRACEON (3604);
DBCC PAGE (YourDatabase, 1, 9, 3) ;

You can download sample corrupted SQL 2008 R2 databases provided by Paul Randal here at this link. Good for testing your DBCC CheckDB script when it detects corruption.

                                       Corruption!!

Another observation (or more a confirmation) is if the DBCC CHECKDB step (or native maintenance plan) encounters corruption, the SQL agent job will fail with errors. This behavior has been my assumption, but some website suggest otherwise. At least now I tested it and confirmed the behavior. Since there is a size limitation on the job history, you may want to consider using WITH NO_INFOMSGS parameter option to suppress information message in order to spare more space for error message.

Update: 1/26/2013
If the database is in READ_ONLY mode, the dbi_dbccLastKnownGood does not get updated on a successful consistency check.