Systems Engineering and RDBMS


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:


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)

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: