Yesterday I was having a discussion with a friend and he said SQL runs a DBCC CheckDB when it starts. He was referring to the message he always noticed in SQL Server log. Surprisingly couple of other colleagues agreed to it. I thought I will write about it.
When a DBCC CheckDB is run, it’s logged in the database header page. When the SQL server is started and database is opened an informational message is printed in the log, reading from that header saying when the last successful run was. Its actually not running a DBCC CheckDB all over again.
If you look at the log message carefully, in the right side it says the last run date/time:
Date 7/4/2014 2:37:44 AM
Log SQL Server (Current – 7/4/2014 9:31:00 AM)
CHECKDB for database ‘master’ finished without errors on 2013-12-01 05:00:02.487 (local time). This is an informational message only; no user action is required.
Now for some reason, if you want to know when was the last DBCC CheckDB was run on a database, you can refer to the start up section of the SQL Log and you can find it out from the log.
Well, start up log not always available and is recycled. In such cases if we want we can query the database header to get the last DBCC CheckDB run. Here is the query from Shankar Reddy to get that info:
CREATE TABLE #temp (
Id INT IDENTITY(1,1),
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