Query CHECKDB history for all databases on a SQL Server

Do you know what Avamar backups are?  No?  Well, neither did I until very recently.  One of my customers uses Dell EMC Avamar for backing up their SQL Server databases.  I have worked with a lot of 3rd party backups, but this was my introduction to Avamar.  Not a big deal.. much like many 3rd party backup solutions, we just need to be sure the configuration and scheduling is correct, and that's that.

But it wasn't.  We've been having frequent unexplained failures, so the customer forwarded me details from their Avamar setup, and this is the first thing that caught my eye:

Consistency checks?!  The backup software is also performing CHECKBs?  Probably fine for some people, but I like my backup software to only do backups.  The CHECKDBs were already running in the daily maintenance, so I feared they may be running twice daily.  This is a quick query of your CHECKDB history, and if you look at the screenshot, you can see each database is being hit twice daily.  Even more interesting -- the 1st CHECKDB on 'ClassPrd' began at 3:10AM and ran for 0:11:57.  The next one began at 3:12AM and ran for 0:12:16.  I'll admit, I did not even know you could run CHECKDB from two different sessions on the same database in parallel.  You can.  I just tested it.  Probably not ideal.  Maybe this is why I was unaware.  😇

Cut/paste into your own SSMS query window and run as-is.  No changes needed.  Of course, you need to run it against a SQL Server where CHECKBs are being performed.

DECLARE

        @default_trace_path VARCHAR(500),

        @tracefilename VARCHAR(500),

        @indx INT;

SET @default_trace_path = (SELECT path FROM sys.traces WHERE is_default = 1);

SET @default_trace_path = REVERSE(@default_trace_path);

SELECT @indx  = PATINDEX('%\%', @default_trace_path);

SET @default_trace_path = REVERSE(@default_trace_path);

SET @tracefilename = LEFT( @default_trace_path,LEN(@default_trace_path) - @indx) + '\log.trc';

SELECT

  SUBSTRING(CONVERT(NVARCHAR(MAX),TEXTData),36, PATINDEX('%executed%',TEXTData)-36) [Command],

  LoginName,

  StartTime,

CONVERT(INT,SUBSTRING(CONVERT(NVARCHAR(MAX),TEXTData),PATINDEX('%found%',TEXTData)+6,PATINDEX('%errors %',TEXTData)-PATINDEX('%found%',TEXTData)-6)) [Errors], CONVERT(INT,SUBSTRING(CONVERT(NVARCHAR(MAX),TEXTData),PATINDEX('%repaired%',TEXTData)+9,PATINDEX('%errors.%',TEXTData)-PATINDEX('%repaired%',TEXTData)-9)) [Repaired],

SUBSTRING(CONVERT(NVARCHAR(MAX),TEXTData),PATINDEX('%time:%',TEXTData)+6,PATINDEX('%hours%',TEXTData)-PATINDEX('%time:%',TEXTData)-6)+':'+SUBSTRING(CONVERT

(NVARCHAR(MAX),TEXTData),PATINDEX('%hours%',TEXTData)+6,PATINDEX('%minutes%',TEXTData)-PATINDEX('%hours%',TEXTData)-6)+':'+SUBSTRING(CONVERT(NVARCHAR

(MAX),TEXTData),PATINDEX('%minutes%',TEXTData)+8,PATINDEX('%seconds.%',TEXTData)-PATINDEX('%minutes%',TEXTData)-8) [Duration]

FROM::fn_trace_gettable( @tracefilename, DEFAULT)

WHERE EventClass = 22 

AND SUBSTRING(TEXTData,36,12) = 'DBCC CHECKDB'

ORDER BY StartTime DESC;

Results from the instance where CHECKDBs were being done twice daily:

Side note;  Do we need to run CHECKDB twice daily?  No.  In my book, you run the integrity checks (CHECKDB) as often as you can, where best case would be daily.  It's a matter of managing the databases proactively, and becoming aware of potential problems in advance, rather than reactively (is that a word?) -- where we suddenly find corruption and have to figure out how bad it is, and how to resolve it -- fast.  

Proactive DBA = Happy DBA.

A couple good reads in this regard:

https://www.brentozar.com/archive/2014/05/dbcc-checkdb-faq/
https://www.brentozar.com/archive/2016/02/how-often-should-i-run-dbcc-checkdb/

A little more about Dell Avamar:
https://www.delltechnologies.com/en-us/data-protection/data-protection-suite/avamar-data-protection-software.htm