Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Verify Last successful CHECKDB on all databases Expand / Collapse
Author
Message
Posted Thursday, March 20, 2008 11:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:34 AM
Points: 194, Visits: 1,154
Comments posted to this topic are about the item Verify Last successful CHECKDB on all databases
Post #472462
Posted Friday, July 3, 2009 4:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:42 AM
Points: 1,287, Visits: 786
Kyle,
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 guaranteed
What is your opinion about this?

regards, Robbert



Post #746921
Posted Thursday, August 13, 2009 4:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:34 AM
Points: 194, Visits: 1,154
Glad 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:

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_date
FROM #DBInfo_LastKnownGoodCheckDB ckdb
INNER JOIN sys.databases db ON ckdb.DatabaseName = db.name
ORDER BY DatabaseName

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!

Kyle
Post #770043
Posted Wednesday, September 28, 2011 7:37 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:20 AM
Points: 8, Visits: 45
I 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 Neier
Date: 3/17/2008
Description: Examines the "boot page" of each database to
express when the last successful CheckDB was performed

*/


SET NOCOUNT ON

CREATE 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 FOR
SELECT name FROM sys.databases WHERE name NOT IN ('tempdb')

OPEN csrDatabases

DECLARE
@DatabaseName varchar(1000),
@SQL varchar(8000)

FETCH NEXT FROM csrDatabases INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

--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 @DatabaseName
END

--Get rid of the rows that I don't care about
DELETE FROM #DBInfo_LastKnownGoodCheckDB
WHERE Field <> '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 HoursSinceGoodCheckDB
FROM #DBInfo_LastKnownGoodCheckDB
ORDER BY DatabaseName


DROP TABLE #DBInfo_LastKnownGoodCheckDB




Thanks,

~DH
Post #1182461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse