Systems Engineering and RDBMS

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.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: