One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.)
So, as is always the case in these situations, I decided to create a script that will return the last time CHECKDB was run against each database.
DECLARE @dbName SYSNAME DECLARE @SQL NVARCHAR(64) CREATE TABLE #DBINFO ( Db SYSNAME NULL ,ParentObj NVARCHAR(60) ,Obj NVARCHAR(60) ,Field NVARCHAR(128) ,Value NVARCHAR(128) ) DECLARE cur CURSOR FOR SELECT d.NAME FROM sys.databases d OPEN cur FETCH NEXT FROM cur INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'DBCC DBINFO([' + @dbName + ']) WITH TABLERESULTS' SELECT @dbName INSERT INTO #DBINFO ( ParentObj ,Obj ,Field ,Value ) EXEC (@SQL) UPDATE #DBINFO SET Db = @dbName WHERE db IS NULL FETCH NEXT FROM cur INTO @dbName END CLOSE cur; DEALLOCATE cur; --DROP TABLE #DBINFO SELECT * FROM #DBINFO WHERE field LIKE '%lastknow%'
This is a useful query within itself, however you may want to automate monitoring the CHECKDB dates. The script below will check for any databases that have not had CHECKDB run against them.
SELECT dbi.Value FROM #dbinfo dbi WHERE field LIKE '%lastknow%' AND ( CASE WHEN isDate(dbi.value) = 1 THEN CAST(dbi.Value AS DATETIME2(3)) END ) < DATEADD(DAY, - 7, GETDATE())
You could possibly add this as a step to a job that runs CHECKDB to monitor for any databases added without you being aware of them. Then you could be alerted via setting up “sp_send_mail”, but if you’ve configured jobs to send emails to operators upon failure you could just configure a RAISERROR if the @@ROWCOUNT of the query returns greater than 0. This will cause the job to fail, which is intended. A sample of the latter option is below.
SELECT dbi.Value FROM #dbinfo dbi WHERE field LIKE '%lastknow%' AND ( CASE WHEN isDate(dbi.value) = 1 THEN CAST(dbi.Value AS DATETIME2(3)) END ) < DATEADD(DAY, - 7, GETDATE()) IF @@ROWCOUNT > 0 RAISERROR ( 'Databases on server not checked using CHECKDB in past 7 days' -- message text ,16 -- severity ,1 -- state )
I’m a much bigger fan of Redgate Monitor than configuring email alerts as it does not clog inboxes, but this alert won’t bark at you like Homer Simpson “Everything’s OK” alarm.
Happy Scripting!Follow @rPh0enix