Last Known Good Backup/DBCC

First published on MSDN on Feb 09, 2010

Based on Paul Randal’s http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-When-did-DBCC-CHECKDB-last-run-successfully.aspx telling how to get the last known good DBCC ran against a database, based on my job requirements, I decided to write a T-SQL script which has nothing new but returns last known good DBCC execution time and last backup time for all databases of a SQL Server instance. The point is that this runs on all three supported versions of SQL Server: 2000, 2005 and 2008 but returns the last successful DBCC execution date for 2005 and 2008 only. By the way, for supported versions have a look at this http://blogs.msdn.com/sqlreleaseservices/archive/2009/10/08/end-of-service-pack-support-for-sql-server-2005-sp2-and-sql-server-2008-rtm.aspx .

In the case you have a 2008 Management Studio installed somewhere on your network, preferably on your monitoring laptop, PC or server, you can register all your SQL Server instances and run this script in a multi-server query.

I have seen a similar http://sqlserverpedia.com/blog/sql-server-bloggers/elegant-solution-finding-last-clean-dbcc-checkdb-ran-date-quickly-for-all-databases/ more compact and elegant using CTE which is just great but cannot work on SQL Server 2000 unfortunately.

This script takes care of SQL Server 2008 doubling the dbi_dbccLastKnownGood field.

Here it is:

SET NOCOUNT ON
GO

USE master
GO

-- Trace flag to make DBCC Page command results available in the current connection
DBCC TRACEON (3604)
GO

CREATE TABLE #DBCC_table (
	ParentObject NVARCHAR(4000) NULL
	,OBJECT NVARCHAR(4000) NULL
	,Field NVARCHAR(4000) NULL
	,VALUE NVARCHAR(4000) NULL
	)

CREATE TABLE #LastDBCC_table (
	[Database Name] NVARCHAR(4000) NULL
	,[Last Known Good DBCC] NVARCHAR(4000) NULL
	)

DECLARE @cmd VARCHAR(4000)
DECLARE @DB_NAME NVARCHAR(500)
DECLARE @DB_ID INT

DECLARE LastDBCC_cursor CURSOR
FOR
SELECT name
	,[dbid]
FROM sysdatabases
ORDER BY dbid

OPEN LastDBCC_cursor

-- Perform the first fetch.
FETCH NEXT
FROM LastDBCC_cursor
INTO @DB_NAME
	,@DB_ID

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
	-- This is executed as long as the previous fetch succeeds.
	SET @cmd = 'dbcc page(' + convert(VARCHAR, @DB_ID) + ',1,9,3) with tableresults'

	INSERT INTO #DBCC_table
	EXECUTE (@cmd)

	INSERT INTO #LastDBCC_table
	SELECT DISTINCT @DB_NAME
		,VALUE
	FROM #DBCC_table
	WHERE Field = 'dbi_dbccLastKnownGood'

	IF @@ROWCOUNT = 0
		INSERT INTO [#LastDBCC_table]
		SELECT @DB_NAME
			,'Not implemented'

	FETCH NEXT
	FROM LastDBCC_cursor
	INTO @DB_NAME
		,@DB_ID

	DELETE #DBCC_table
END

CLOSE LastDBCC_cursor

DEALLOCATE LastDBCC_cursor

SELECT T1.[Database Name]
	,CASE 
		WHEN (max(T1.[Last Known Good DBCC]) = '1900-01-01 00:00:00.000')
			THEN 'Not Yet Ran'
		ELSE max(T1.[Last Known Good DBCC])
		END AS [Last Known Good DBCC]
	,
	--max(T1.[Last Known Good DBCC]) as [Last Known Good DBCC],
	COALESCE(convert(VARCHAR(50), MAX(T2.backup_finish_date), 21), 'Not Yet Taken') AS [Last BackUp Taken]
FROM #LastDBCC_table T1
LEFT OUTER JOIN msdb.dbo.backupset T2 ON T2.database_name = T1.[Database Name]
GROUP BY T1.[Database Name]
ORDER BY T1.[Database Name]

DROP TABLE #LastDBCC_table

DROP TABLE #DBCC_table

DBCC TRACEOFF (3604)
GO

Lionel Pénuchot

Senior Premier Field Engineer, Microsoft France