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.
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.
If the database is in READ_ONLY mode, the dbi_dbccLastKnownGood does not get updated on a successful consistency check.