Get last time Integrity Checks ran successfully on all databases

One of our responsibilities as DBAs is to perform all kind of maintenance tasks in our databases. Sometimes we create jobs and once these jobs work fine for some weeks we just forget about them and assume they’re all running fine all the time. Of course, you set alerts in case one of those jobs failed but in environments where there are hundreds of production instances and thousands of databases you or your team may simply lose some of them.

So, you’re troubleshooting a database one day and you suspect you have an issue related to corruption, you need to know when was the last time Integrity Checks ran successfully in this database if any.

No rocket science here, we all know you just execute:

DBCC DBINFO('TestDB') WITH TABLERESULTS
GO

Once you executed that query you received a big result set and start looking for “dbi_dbccLastKnownGood” field. Once you find it you just take a look at value field and well that’s the last date when Integrity Checks ran fine.

Now you want to do this for all the databases in the instance, if the instance has 3 or 4 user databases it won’t be difficult but what about an instance with 20, 50 or +100 databases? Not so easy now, right?

I had this necessity once while working with one client; therefore, I wrote a query that will gather this info from all databases in the instance and display the results easily and clean.

/******************************************************************************
Author: Victor Sosa
Blog: www.wisesql.com

Description: Get last known good DBCC CHECKDB() for all databases.
******************************************************************************/

CREATE TABLE #LastKnownGoodDBCC
(
	[ParentObject] nvarchar(150),
	[Object] nvarchar(150),
	[Field] nvarchar(100),
	[VALUE] nvarchar(150)
)
GO

CREATE TABLE #Results
(
	[DBName] nvarchar(128),
	[Field] nvarchar(100),
	[VALUE] nvarchar(150)
)
GO

sp_MSforeachdb '
	INSERT INTO #LastKnownGoodDBCC
	EXECUTE (''DBCC DBINFO(''''?'''') WITH TABLERESULTS'')

	INSERT INTO #Results
	SELECT ''?'', [Field], [VALUE] FROM #LastKnownGoodDBCC
	WHERE [Field] = ''dbi_dbccLastKnownGood''

	DELETE FROM #LastKnownGoodDBCC
	'

SELECT
	DBName, Field, [Value]
FROM
	#Results
ORDER BY
	DBName
GO

DROP TABLE #LastKnownGoodDBCC
GO
DROP TABLE #Results
GO

I used this query often now and you can even include it to make some automation on your environment, hope it works for you as it has worked for me.

This is an image of the results on my Instance:

Last_Known_Good_CheckDB

Tip: If you see a date value = 1900-01-01 it means the database has never gone through Integrity Checks maintenance before.

See you next time.

  1. Good Job Víctor!!

    Like

    Reply

    1. Thanks Jesus. Hope it helps some DBAs.

      Like

      Reply

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

%d bloggers like this: