T-sql – Date of Last DBCC checkdb run

dbcc-checkdbmaintenancet-sql

Using T-SQL, I can get the last known good DBCC Checkdb through DBCC DBINFO().
My question is, can I find, somewhere in SQL, when the Last DBCC Checkdb Ran? (regardless of being successful or not)

I would need this info to compare the Last date it ran with the last known good DBCC checkdb so that I can take action if the dates don't match.

Since I'm on SQL Express, I can't use SQL Server Agent to run the DBCC checkdb so it can be run randomly.

Any help is greatly appreciated.
Thank you

Best Answer

One way to achieve is to log information from your default trace into a physical persistent table.

A default trace is like a black box recorder for sql server and is ON by default. It records 116 (EventID ) Audit DBCC Event (Event Description).

Only relying on dbi_dbccLastKnownGood for determining of checkdb ran is misleading as DBCC CHECKDB ('your_db_name') WITH PHYSICAL_ONLY will update the dbi_dbccLastKnownGood along with DBCC CHECKFILEGROUP.

The only way to determine of checkdb ran successfully is to actually run it and record all the error messages (if any).

Also, refer to : What event information can I get by default from SQL Server? from Aaron Bertrand.

Related Question