SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Verify Last successful CHECKDB on all databases


Verify Last successful CHECKDB on all databases

Author
Message
Kyle Neier ,
Kyle Neier ,
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 1188
Comments posted to this topic are about the item Verify Last successful CHECKDB on all databases
Robbert Hof
Robbert Hof
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 834
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



Kyle Neier ,
Kyle Neier ,
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 1188
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
Derrick H.
Derrick H.
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 61
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
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35226 Visits: 886
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search