DBCC CHECKDB Job
Posted by decipherinfosys on January 2, 2009
As part of the maintenance routines, we have always stored the output of the DBCC executions in order to better track the work done, schedule alerts, have reports based off that data etc.. Recently, at a client site, the DBA was running DBCC commands at random intervals and since he had left, no one was sure when was the last time the DBCC checks were run. There is a way to do this from SQL Server 2005 onwards. We would first need to turn on the trace flag 3604:
DBCC TRACEON (3604)
GO
The information pertaining to the last run is kept at the boot page of the database which is page #9.
You can execute the DBCC PAGE command next:
DBCC PAGE (decipher_test, 1, 9, 3)
GO
Then look for the value next to this field: “dbi_dbccLastKnownGood” and if you see the date as: “1900-01-01 00:00:00.000″, it means that DBCC has not been run on that database. If you see some other date, then that is the last time that it was run against that database without any corruption being reported by that run.
In the above scenario, the DBA did not have a job running for the DBCC CHECKDB command. If you do have a job running and if the execution of DBCC CHECKDB fails with corruption messages, the job itself will also fail. However, the history for the job will not give you all the output from that run so troubleshooting becomes an issue. That is why we recommend that as part of the script, you should also store the output in some location.
