Wednesday, 31 August 2011

Checking CHECKDB !

When reviewing some agent logs this morning it struck me that the maintenance plans had run a little quickly. The agent job hadn't failed, but the run duration seemed remarkably short (considering the database size). The plan concerned was one of those 'all maintenance in one step' jobs hence I couldn't quantify how the run duraton was comprised. Backups were present in their directory, so one of the other steps must have silently failed.

Running a server audit script picked up Integrity Checks as not having occurred. My audit script uses this piece of code to get the date.

CREATE TABLE #temp (        
       [ParentObject]    VARCHAR(255)
       , [Object]       VARCHAR(255)
       , Field          VARCHAR(255)
       , [Value]        VARCHAR(255) 


SELECT DISTINCT Value AS DBCCDate FROM #temp WHERE Field = 'dbi_dbccLastKnownGood'

There are 2 caveats with this code
  1. It only works on SQL 2005+
  2. It needs DISTINCT in the select statement to run on SQL 2008 (where a bug means the date is reported twice).
So, having found that the DBCC checks had not occurred, i turned to google :/
It turns out the client were running SQL 2005 SP2, build 9.00.3042.
In this build there is a bug re; integrity checks failing in maintenance plans.

No comments: