Last clean DBCC CHECKDB date

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Transact SQL Code Library

Contents

Introduction

One of the primary responsibility for a DBA is to make sure the intergrity of the database is maintained at all times. Database integrity is checked using DBCC CHECKDB or by running DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG individually. Its a good practise to run integrity checks on a regular schedule and its even more important to know the last ran clean DBCC CHECKDB date in case corruption has creeped into the system.

Pre SQL Server 2005

In versions prior to SQL Server 2005, the information about the last DBCC CHECKDB date is logged in the SQL Server Error logs when DBCC CHECKDB completes successfully. If the error logs are maintained long enough then one can traverse the error logs to find the last ran clean DBCC CHECKDB date. The solutions to retrieve this information can be clumsy.

SQL Server 2005 and later

Starting from SQL Server 2005, the last ran clean DBCC CHECKDB date is written in the boot page of the database when DBCC CHECKDB completes without finding any corruption. And note that this information is written in the error logs when the instance is re-started by looking at the boot page. Boot page is always the 9th page in the database.

How to retrieve the last ran clean DBCC CHECKDB for one database?

/*Set the trace flag to redirect the output to the window*/
DBCC TRACEON (3604); 
GO

/*Use DBCC PAGE to look at the boot page i.e #9 */
DBCC PAGE (AdventureWorks, 1, 9, 3);
GO

One has to look for dbi_dbccLastKnownGood value from the results of the above command. 

/*
	m_fSeqNo = 42                        m_blockOffset = 1363                 m_slotId = 1
	dbi_RebuildLogs = 0                  dbi_dbccFlags = 2                    
	dbi_dbccLastKnownGood = 2009-09-27 10:22:35.187                           
	dbi_dbbackupLSN
*/

How to retrieve the last ran clean DBCC CHECKDB for many databases?

One of the two below methods can be used to retrieve the information about the last ran clean DBCC CHECKDB date for all the databases on a server. 

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

INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';

;WITH CHECKDB1 AS
(
    SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbi_dbname'))
    ,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbi_dbccLastKnownGood')
)      
SELECT CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2

DROP TABLE #temp

Here is another method to retrieve the above information. 

IF OBJECT_ID('dbo.GetLastRanCleanDBCCCHECKDBForAllDatabases') IS NOT NULL
BEGIN    
DROP PROCEDURE dbo.GetLastRanCleanDBCCCHECKDBForAllDatabases;
END
GO
CREATE PROCEDURE dbo.GetLastRanCleanDBCCCHECKDBForAllDatabases AS
BEGIN   
   SET NOCOUNT ON;
   CREATE TABLE #temp (          
       ParentObject     VARCHAR(255)
       , [Object]       VARCHAR(255)
       , Field          VARCHAR(255)
       , [Value]        VARCHAR(255)   
   )   
   
   CREATE TABLE #DBCCResults (
        ServerName           VARCHAR(255)
        , DBName             VARCHAR(255)
        , LastCleanDBCCDate  DATETIME   
    )   
    
    EXEC master.dbo.SP_MSFOREACHDB       
           @Command1 = 'USE ? INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')'
           , @Command2 = 'INSERT INTO #DBCCResults SELECT @@SERVERNAME, ''?'', value FROM #temp WHERE field = ''dbi_dbccLastKnownGood'''
           , @Command3 = 'TRUNCATE TABLE #temp'   
   
   --Delete duplicates due to a bug in SQL Server 2008
   
   ;WITH DBCC_CTE AS
   (
       SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DBName, LastCleanDBCCDate ORDER BY LastCleanDBCCDate) RowID
       FROM #DBCCResults
   )
   DELETE FROM DBCC_CTE WHERE RowID > 1;
   
    SELECT        
           ServerName       
           , DBName       
           , CASE LastCleanDBCCDate 
                   WHEN '1900-01-01 00:00:00.000' THEN 'Never ran DBCC CHECKDB' 
                   ELSE CAST(LastCleanDBCCDate AS VARCHAR) END AS LastCleanDBCCDate    
   FROM #DBCCResults   
   
   DROP TABLE #temp, #DBCCResults;
END

The right way in future?

A connect item has been created by Victor Isakov to expose the last ran clean DBCC CHECKDB date in SYS.DATABASES catalog view. If you find this information will be useful if exposed in SYS.DATABASES then vote for this Adding when DBCC CHECKDB last ran to the SYS.DATABASES catalog view

Recommended reading

Author Credits

Sankar Reddy

This wiki article is created by Sankar Reddy. Sankar is a Database Engineer and an involuntary DBA working for Funmobility, a mobile media and entertainment company where he works with high traffic OLTP systems and over 1 TB data warehouse. Sankar strongly believes in 'Every day is a school day!' and he's thankful for the vibrant SQL Server community. His online presences include:

Retrieved from "http://sqlserverpedia.com/wiki/Last_clean_DBCC_CHECKDB_date"