Last Time CheckDB was Run

Back in November 2012, I shared a story about checking the last known good checkdb in the boot page of a database. You can read that story here.

This is an important enough topic that it is worth repeating frequently if I wanted to do that. If for no other reason than to continue to hammer at how important it is to both run checkdb and know the last time that checkdb was run successfully.

Alas, I am writing to fix a few things with the script that I shared in that last past.

I run this script on every server I touch to get a report for the last known good checkdb for every single database. I had been running the script flawlessly across many servers without error. Then it happened. The script failed with a nasty error.

After a bit of looking, it became apparent my flaw in the script. I had not written the script with CS (case sensitivity) in mind. I touch so few CS servers, that I sometimes forget to check for that. Slap my hands and I will work on that going forward.

So here is the update to the script.

[codesyntax lang=”tsql”]

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

CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum	INT
)

DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);

DECLARE dbccpage CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
	WHERE 1 = 1
		AND state = 0
		--And name NOT IN ('tempdb')
	;

OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@Fetch_Status = 0
BEGIN
	SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13)
	SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13)

	INSERT INTO #temp
		EXECUTE (@SQL);
	SET @SQL = ''

	INSERT INTO #DBCCRes
			( DBName, dbccLastKnownGood,RowNum )
		SELECT @DBName, VALUE
				, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
			FROM #temp
			WHERE Field = 'dbi_dbccLastKnownGood';

	TRUNCATE TABLE #temp;

	FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;

SELECT DBName,dbccLastKnownGood
	FROM #DBCCRes
	WHERE RowNum = 1;

DROP TABLE #temp
DROP TABLE #DBCCRes

CREATE TABLE #temp (

Id INT IDENTITY(1,1),

ParentObject VARCHAR(255),

[Object] VARCHAR(255),

Field VARCHAR(255),

[VALUE] VARCHAR(255)

)

CREATE TABLE #DBCCRes (

Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,

DBName sysname ,

dbccLastKnownGood DATETIME,

RowNum INT

)

DECLARE

@DBName SYSNAME,

@SQL VARCHAR(512);

DECLARE dbccpage CURSOR

LOCAL STATIC FORWARD_ONLY READ_ONLY

FOR SELECT name

FROM sys.databases

WHERE 1 = 1

AND state = 0

--And name NOT IN ('tempdb')

;

OPEN dbccpage;

FETCH NEXT FROM dbccpage INTO @DBName;

WHILE @@Fetch_Status = 0

BEGIN

SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13)

SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13)

INSERT INTO #temp

EXECUTE (@SQL);

SET @SQL = ''

INSERT INTO #DBCCRes

( DBName, dbccLastKnownGood,RowNum )

SELECT @DBName, VALUE

, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum

FROM #temp

WHERE Field = 'dbi_dbccLastKnownGood';

TRUNCATE TABLE #temp;

FETCH NEXT FROM dbccpage INTO @DBName;

END

CLOSE dbccpage;

DEALLOCATE dbccpage;

SELECT DBName,dbccLastKnownGood

FROM #DBCCRes

WHERE RowNum = 1;

DROP TABLE #temp

DROP TABLE #DBCCRes

[/codesyntax]

9 thoughts on “Last Time CheckDB was Run”

Great script. I’ve been meaning to rewrite mine as it uses sp_msforeachdb which has problems.

Just curious, why do you filter out tempdb? Tempdb can get corrupted too.

One suggestion, it fails if there is a database offline (mirror, log shipping secondary, etc). Recommend adding “state = 0” to the query of sys.databases.

Jason Brimhall says:

January 17, 2013 at 10:39

I waffled on filtering out offline databases or not. I’ll add it in. I was excluding tempdb only because each time the instance is restarted, boot page is restored and the dbi_dbccLastKnownGood is restored back to 1/1/1900.
But I agree that tempdb can be corrupt and if the server hasn’t been restarted for a while then we need to know the last time checkdb was run against tempdb. I’ll change that filter.

Reply

I actually wrote similar code and it got added to Brent Ozar’s sp_blitz procedure. Someone found a bug – not in my code, but in the output of DBCC DBInfo, which returns a row for dbi_dbccLastKnownGood twice in SQL 2008+. Looks like your version using DBCC Page doesn’t suffer from this problem. Nice.

Jason Brimhall says:

January 22, 2013 at 16:28

That’s pretty cool.

Reply

Correction..Looks like the bug still happens with DBCC Page (I tested on a 2005 server by mistake), but I see that’s why you select WHERE RowNum = 1…

Jason Brimhall says:

January 22, 2013 at 16:26

Yeah. I was seeing that bug. I had to back all the way out and examine the DBCC Page results to confirm on a single database to make sure it wasn’t something I had done. But the rownum was an easy enough fix.

Reply