Last Known Good Backup/DBCC

[SQLPFE]

First published on MSDN on Feb 09, 2010

Based on Paul Randal’s post 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 blog entry .

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