Last Known Good Backup/DBCC

  • [アーティクル]

 

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

Comments

  • Anonymous
    February 13, 2010
    This will not work for SQL 2000, when you do a DBCC PAGE (YourDB,1,9,3) there is no dbi_dbcclastknowngood field to get this.

  • Anonymous
    February 23, 2010
    You are right, the script did take care of it in the sence that the Last known DBCC was only reported for SQL 2005/2008 but the script itself does run for all versions. For SQL 2000, it will report it as 'not implemented'

  • Anonymous
    March 10, 2011
    Hello I leave this code is similar to the previous mode in automatic sweep all databases that contain the instance Greetings CREATE TABLE #temp (             ParentObject     VARCHAR(255)     , [Object]       VARCHAR(255)     , Field          VARCHAR(255)     , [Value]        VARCHAR(255)   )     CREATE TABLE #DBCCResults (   ServerName           VARCHAR(255)   , SQLip    VARCHAR(15)   , DBName             VARCHAR(255)   , DBSize             FLOAT   , LastCleanDBCCDate   DATETIME       , Days   DATETIME       )     EXEC master.dbo.SP_MSFOREACHDB           @Command1 = 'USE ? INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')'     , @Command2 = 'INSERT INTO #DBCCResults SELECT @@SERVERNAME, (select local_net_address from sys.dm_exec_connections where session_id = @@spid) as SQLIP, ''?'', (select (convert (dec (15,2),sum(convert(bigint,case when status & 64 = 0 then size else 0 end))) + convert (dec (15,2),sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)))) * 8192 / 1048576 from ?.dbo.sysfiles) , value, 0 FROM #temp WHERE field = ''dbi_dbccLastKnownGood'''     , @Command3 = 'TRUNCATE TABLE #temp'     ;WITH DBCC_CTE AS   (     SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DBName, LastCleanDBCCDate ORDER BY LastCleanDBCCDate) RowID     FROM #DBCCResults   )   DELETE FROM DBCC_CTE WHERE RowID > 1;   SELECT             ServerName       , SQLip             , DBName           , convert(float,DBSize) as DBSize       , CASE LastCleanDBCCDate     WHEN '1900-01-01 00:00:00.000' THEN 'Nunca se ha ejecutado DBCC CHECKDB'     ELSE CONVERT(CHAR,LastCleanDBCCDate,120) END AS LastCleanDBCCDate         , CONVERT(INT, (GETDATE() - LastCleanDBCCDate)) AS Days   FROM #DBCCResults  WHERE DBName not in ('master','model','tempdb','msdb') and DBName not like '%report%'   DROP TABLE #temp, #DBCCResults;

  • Anonymous
    April 13, 2011
    I was aware of the dbcc dbinfo with tableresults for this type of thing, but the main script simplifies the output.  Great work.  And of course, Paul is the Dean of DBCC :-)   Thx