THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

When did DBCC CHECKDB last run on my databases?

I've seen this question a few times on the forums, and unfortunately there never is a really good answer that is easy to use.  SQL Server 2000 didn't track this information internally, and even though an internal tracking mechanism was added to SQL Server 2005, it isn't the easiest thing to get for every database in a large environment.  See Paul Randal's blog posting:

CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?

What I figured out when looking at this in SQL Server 2000 is that DBCC CHECKDB runs, an event is logged in the SQL Server Error Logs.  So if you keep you logs long enough, and you run CHECKDB appropriately, you should be able to scan the logs to find out the last time it ran.  I personally maintain twenty-eight log files on my production servers, but I roll my log over every day as a part of a monitoring process.  You can find out how to accomplish this on the following article:

Monitor the SQL Server Error Log

The default for SQL Server is to only rollover the ErrorLog when the Service restarts, and to retain the last six logs on the server.  So unless you have had significant problems with SQL lately, you should have at least a few months of logs available on the server.  To read the log information we can use sp_readerrorlog system stored procedure in master which accepts a log number parameter as an input allowing you to search older logs as well as the active log, and output its results set into a temporary table.  .  To determine how many logs there are, you can call the sp_numerrorlogs system stored procedure, and output its result set into a temporary table as well.

The following is an example that works in SQL Server 2000, 2005, and 2008:

SET NOCOUNT OFF

DECLARE
@sql VARCHAR(4000)
DECLARE @lognumber INT
DECLARE
@logcount INT

SET
@lognumber = 0

-- create temp tables to hold the information
CREATE TABLE #dbcc
(rowid INT IDENTITY PRIMARY KEY, entrytime DATETIME,
source VARCHAR(50), logentry VARCHAR(4000))

  
CREATE INDEX [dbcc_logentry]
      
ON dbo.[#dbcc](logentry)
      
WITH FILLFACTOR = 70

CREATE TABLE #errorlog
(logentry VARCHAR(4000), continuationrow INT)

CREATE TABLE #avail_logs
(lognum INT, logdate DATETIME, logsize INT)

-- get the number of available logs
INSERT INTO #avail_logs
EXEC sp_enumerrorlogs

SELECT @logcount = MAX(lognum)
FROM #avail_logs

WHILE @lognumber < @logcount
BEGIN
  
-- clear the previous logs results
  
TRUNCATE TABLE #errorlog

  
SELECT @SQL = 'exec master..sp_readerrorlog ' + CONVERT(VARCHAR(3),@lognumber)
  

  
IF @@version LIKE '%2000%'
  
BEGIN
      
-- load the data from the errorlog
      
INSERT INTO #errorlog (logentry, continuationrow)
      
EXECUTE (@SQL)

      
-- store each interesting entry
      
INSERT INTO #dbcc (entrytime, source, logentry)
      
SELECT
             
CASE WHEN LEFT(logentry, 3) = '200' THEN CONVERT(DATETIME, SUBSTRING(logentry, 0, 23))
                  
ELSE NULL
              
END AS entrytime,
             
CASE WHEN LEFT(logentry, 3) = '200' THEN SUBSTRING(logentry, 24, 10)
                  
ELSE NULL
              
END AS source,
             
CASE WHEN LEFT(logentry, 3) = '200' THEN SUBSTRING(logentry, 34, 300)
                  
ELSE logentry
              
END AS logentry
        
FROM #errorlog
      
WHERE logentry LIKE '%DBCC CHECKDB%'
  
END
   ELSE

  
BEGIN
      
-- load the data from the errorlog
      
INSERT INTO #dbcc (entrytime, source, logentry)
      
EXECUTE (@SQL)

      
-- store each interesting entry
      
DELETE #dbcc
      
WHERE logentry NOT LIKE '%DBCC CHECKDB%'
  
END

  
-- move to the next log
  
SET @lognumber = @lognumber + 1
END

-- output the last entry for each database
SELECT name, MAX(entrytime)
FROM sysdatabases
LEFT JOIN #dbcc ON logentry LIKE '%' + name + '%'
GROUP BY name

-- clean up temp tables
DROP TABLE #dbcc
DROP TABLE #errorlog
DROP TABLE #avail_logs

Hope it helps someone.

Published Wednesday, January 28, 2009 11:32 AM by Jonathan Kehayias

Comments

 

Denis Gobo said:

just some additional info

on 2005 and 2008 you can also do this

xp_ReadErrorLog 0, 1, 'dbcc', 'checkdb'

the first param (0) is the log number

the second param (1) tells it to use the error log (1) or the agent log (2)

the next two params can be combined to search for specific stuff

you could have also done the following

xp_ReadErrorLog 0, 1, 'dbcc checkdb'

instead of

xp_ReadErrorLog 0, 1, 'dbcc', 'checkdb'

January 28, 2009 1:58 PM
 

Sankar Reddy said:

Jonathan,

I missed this post earlier and I blogged the same topic this week. Here is a variation that I came up with. I acknowledged your indirect help (SP_MSFOREACHDB) on the blog post.

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

http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!383.entry

June 16, 2009 3:27 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement