InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 13:05:52 GMT20http://www.sqlservercentral.com/Forums/Topic472462-1231-1.aspxI really like this script but added the print output to allow me to execute a checkdb physical_only on all of the databases./*Author: Kyle NeierDate: 3/17/2008Description: Examines the "boot page" of each database to express when the last successful CheckDB was performed*/SET NOCOUNT ONCREATE TABLE #DBInfo_LastKnownGoodCheckDB ( ParentObject varchar(1000) NULL, Object varchar(1000) NULL, Field varchar(1000) NULL, Value varchar(1000) NULL, DatabaseName varchar(1000) NULL )DECLARE csrDatabases CURSOR FAST_FORWARD LOCAL FORSELECT name FROM sys.databases WHERE name NOT IN ('tempdb')OPEN csrDatabasesDECLARE @DatabaseName varchar(1000), @SQL varchar(8000)FETCH NEXT FROM csrDatabases INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGIN --Create dynamic SQL to be inserted into temp table SET @SQL = 'DBCC DBINFO (' + CHAR(39) + @DatabaseName + CHAR(39) + ') WITH TABLERESULTS' --Create Statement to execute a physical only checkdb print '------------------------------------------------------------------- dbcc checkdb(' + char(39) + @DatabaseName + char(39) + ') with physical_only;-------------------------------------------------------------------/*' --Insert the results of the DBCC DBINFO command into the temp table INSERT INTO #DBInfo_LastKnownGoodCheckDB (ParentObject, Object, Field, Value) EXEC(@SQL) --Set the database name where it has yet to be set UPDATE #DBInfo_LastKnownGoodCheckDB SET DatabaseName = @DatabaseName WHERE DatabaseName IS NULL print '*/'FETCH NEXT FROM csrDatabases INTO @DatabaseNameEND--Get rid of the rows that I don't care aboutDELETE FROM #DBInfo_LastKnownGoodCheckDBWHERE Field &lt;&gt; 'dbi_dbccLastKnownGood'SELECT DatabaseName, CAST(Value AS datetime) AS LastGoodCheckDB, DATEDIFF(dd, CAST(Value AS datetime), GetDate()) AS DaysSinceGoodCheckDB, DATEDIFF(hh, CAST(Value AS datetime), GetDate()) AS HoursSinceGoodCheckDBFROM #DBInfo_LastKnownGoodCheckDBORDER BY DatabaseNameDROP TABLE #DBInfo_LastKnownGoodCheckDBWed, 28 Sep 2011 07:37:04 GMTDerrick H.http://www.sqlservercentral.com/Forums/Topic472462-1231-1.aspxGlad to hear someone else is getting good use out of this. I've noticed a similar behavior with restored databases. I've never let that concern me. However, you could make it a policy to always do a checkdb immediately after a create database statement. The db will be small, so it should finish in a few seconds. As for a more precise monitoring tool, you could bring the create_date from the sys.databases table into the query. You could then invalidate the false positive if the create_date was after the checkdb date:[code]SELECT DatabaseName, CAST(Value AS datetime) AS LastGoodCheckDB, DATEDIFF(dd, CAST(Value AS datetime), GetDate()) AS DaysSinceGoodCheckDB, DATEDIFF(hh, CAST(Value AS datetime), GetDate()) AS HoursSinceGoodCheckDB, db.create_dateFROM #DBInfo_LastKnownGoodCheckDB ckdb INNER JOIN sys.databases db ON ckdb.DatabaseName = db.nameORDER BY DatabaseName[/code]Although I don't know every action that modifies the create_date column, I do know that sp_renamedb and alter database modify file do. If you use these functions, expect a new problem of false negatives.Hope that helps!KyleThu, 13 Aug 2009 04:49:37 GMTKyle Neier ,http://www.sqlservercentral.com/Forums/Topic472462-1231-1.aspxKyle,I use this procedure as part of my dbhealth check.For newly created databases (with no checkdb done) I found that the value for dbi_dbccLastKnownGood is the same as the value for the model database.In a way this is correct, because a new database is a copy of the model database. On the other hand the checkdb is not executed on the new database itself and therefore a healthy database is not guaranteedWhat is your opinion about this?regards, RobbertFri, 03 Jul 2009 04:57:19 GMTRobbert Hofhttp://www.sqlservercentral.com/Forums/Topic472462-1231-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Administration/62598/">Verify Last successful CHECKDB on all databases</A>[/B]Thu, 20 Mar 2008 11:48:05 GMTKyle Neier ,