1

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

10
  • Just because there is no agent does not mean you can't (or shouldn't) schedule regularly nightly or weekly dbcc through other means rather than relying on random, memory, etc. Windows Task Scheduler, PowerShell jobs, etc. Oct 24 '16 at 18:54
  • default trace logs that info provided its not rolled over or recycled. check EventClass = 116 AND TextData like '%DBCC%'
    – Kin Shah
    Oct 24 '16 at 18:54
  • 1
    Also, if you're not relying on automation, I have a hard time believing someone is running checkdb manually, getting errors, and deciding not to tell anyone... Oct 24 '16 at 18:55
  • @kin not sure i understand "EventClass = 116 AND TextData like '%DBCC%'" are you talking about using the xp_enumerrorlogs ? I was hoping that there was another way something like the DBINFO() that shows the last known good DBCC. I want to try to create something automated that will compare the last run with the last known good dates.
    – JohnG
    Oct 24 '16 at 19:07
  • 1
    Ok, so you just want to run dbcc checks when someone remembers? Sounds great. As for querying the default trace, did you search? Here's one example (you'll have to change the where clause of course): sqlblog.com/blogs/aaron_bertrand/archive/2011/02/02/… Oct 24 '16 at 19:52
3

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.

5
  • As I was searching more and more, I found the following: If I compare "EXEC sys.xp_readerrorlog 0,1,N'DBCC CHECKDB', N'MyDB'" against the DBINFO's Last known Good DBCC... Wouldn't that do the job? If the dates match , it's good, otherwise it's no good. Do you see a problem with this?
    – JohnG
    Oct 24 '16 at 20:32
  • 1
    It would do as well. default trace is less in size as opposed to error log and it has less events logged as well. Also, as I mentioned in my answer, the dbi_dbccLastKnownGood is not 100% reliable so use it with caution. Why not schedule a job using powershell and then check the successful output .. instead of relying on something that is not 100% accurate ?
    – Kin Shah
    Oct 24 '16 at 21:48
  • Powershel requires XP_CMDSHELL and that is a whole other debate and won't pass here. Why isn't dbccLastKnowGood 100% reliable?
    – JohnG
    Oct 26 '16 at 17:30
  • Did you read my linked answer ?
    – Kin Shah
    Oct 26 '16 at 20:26
  • yes I have... and it answered my question
    – JohnG
    Nov 9 '16 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.