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!!

    Me gusta

    Responder

    1. Thanks Jesus. Hope it helps some DBAs.

      Me gusta

      Responder

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: