Making “Check Last CHECKDB Date” A LOT Easier

One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.)
So, as is always the case in these situations, I decided to create a script that will return the last time CHECKDB was run against each database.

DECLARE @dbName SYSNAME
DECLARE @SQL NVARCHAR(64)

CREATE TABLE #DBINFO (
	Db SYSNAME NULL
	,ParentObj NVARCHAR(60)
	,Obj NVARCHAR(60)
	,Field NVARCHAR(128)
	,Value NVARCHAR(128)
	)

DECLARE cur CURSOR
FOR
SELECT d.NAME
FROM sys.databases d

OPEN cur

FETCH NEXT
FROM cur
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'DBCC DBINFO([' + @dbName + ']) WITH TABLERESULTS'

	SELECT @dbName

	INSERT INTO #DBINFO (
		ParentObj
		,Obj
		,Field
		,Value
		)
	EXEC (@SQL)

	UPDATE #DBINFO
	SET Db = @dbName
	WHERE db IS NULL

	FETCH NEXT
	FROM cur
	INTO @dbName
END

CLOSE cur;

DEALLOCATE cur;

--DROP TABLE #DBINFO
SELECT *
FROM #DBINFO
WHERE field LIKE '%lastknow%'


This is a useful query within itself, however you may want to automate monitoring the CHECKDB dates. The script below will check for any databases that have not had CHECKDB run against them.


SELECT dbi.Value
FROM #dbinfo dbi
WHERE field LIKE '%lastknow%'
	AND (
		CASE 
			WHEN isDate(dbi.value) = 1
				THEN CAST(dbi.Value AS DATETIME2(3))
			END
		) < DATEADD(DAY, - 7, GETDATE())


You could possibly add this as a step to a job that runs CHECKDB to monitor for any databases added without you being aware of them. Then you could be alerted via setting up “sp_send_mail”, but if you’ve configured jobs to send emails to operators upon failure you could just configure a RAISERROR if the @@ROWCOUNT of the query returns greater than 0. This will cause the job to fail, which is intended. A sample of the latter option is below.

SELECT dbi.Value
FROM #dbinfo dbi
WHERE field LIKE '%lastknow%'
	AND (
		CASE 
			WHEN isDate(dbi.value) = 1
				THEN CAST(dbi.Value AS DATETIME2(3))
			END
		) < DATEADD(DAY, - 7, GETDATE()) IF @@ROWCOUNT > 0
	RAISERROR (
			'Databases on server not checked using CHECKDB in past 7 days' -- message text
			,16 -- severity
			,1 -- state
			)

I’m a much bigger fan of Redgate Monitor than configuring email alerts as it does not clog inboxes, but this alert won’t bark at you like Homer Simpson “Everything’s OK” alarm.

Happy Scripting!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

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