how to find out how long the last DBCC checkdb took to complete?

Question

In most of my servers I have dbcc checkdb scheduled to run.

I can get the last known good DBCC Checkdb through DBCC DBINFO().

I can read the tracks left on the sql server log and I get something like the picture below:

Or I can run the following script:

IF OBJECT_ID('TEMPDB..#MAINT') IS NOT NULL
   DROP TABLE #maint

CREATE TABLE #maint (Results varchar(255) NULL)
INSERT INTO #maint(Results) EXEC('master..xp_cmdshell ''sqlcmd -E -Q"dbcc checkdb([my_database])"''')

select * from #maint 

and get something like this:

I could also have a look at the default trace.

what am I after?

How to find out how long did it take to run dbcc checkdb the last time.

Answer 1

If it is scheduled, then getting it from the SQL Agent job's runtime duration is an option.

And if you're using Ola's solution (which I'd recommend) then his dbo.CommandLog table has StartTime and EndTime columns for all the actions it performs, including DBCC work.

Answer 2

If I understand correctly

EXEC sp_readerrorlog 0, 1, 'dbcc' will give you the raw data you want.

To neaten it up you could use something like

DECLARE @tempLog as Table (logdate datetime, theSpid varchar(10), theText varchar(max))
INSERT INTO @tempLog  EXEC sp_readerrorlog 0, 1, 'dbcc'
SELECT 
SUBSTRING(thetext,CHARINDEX('(',theText) + 1,(CHARINDEX(')',theText)+LEN(')'))-CHARINDEX('(', theText) - 2) as theDatabase,
SUBSTRING(thetext,CHARINDEX('Elapsed Time:',theText),(CHARINDEX('seconds',theText)+LEN('seconds'))-CHARINDEX('Elapsed time: ', theText)) as DBBC_CheckDB_Time
FROM @tempLog 

Hope that helps