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 lost 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 to 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
WHERE
Field = 'dbi_dbccLastKnownGood'
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.



Categories: CHECKDB

Tags: , , ,

2 replies

  1. Good Job Víctor!!

    Like

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: