MS SQL Server – Determine last date DBCC CheckDB was ran

MS SQL Server – Determining last date DBCC CheckDB was ran

A very good article around determine the last time DBCC CheckDB date was ran against a Database is available @ http://sqlserverpedia.com/wiki/Last_clean_DBCC_CHECKDB_date

   SET NOCOUNT ON;

   If object_id('tempdb.dbo.#temp') is not null

   begin

      drop table #temp

   end</pre>
<pre>   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, NO_INFOMSGS'')'
           , @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      
           , Max(LastCleanDBCCDate) as LastCleanDBCCDate
           , CASE Max(LastCleanDBCCDate)
                   WHEN '1900-01-01 00:00:00.000' THEN 'Never ran DBCC CHECKDB'
                   ELSE Convert(varchar(255), max(LastCleanDBCCDate))
             END AS LastCleanDBCCDateInfo   
   FROM #DBCCResults  
   Group by
             ServerName      
           , DBName      
   Order by
             ServerName      
           , DBName         
   DROP TABLE #temp, #DBCCResults;

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s