SQL Server – How to Find Duration of Last DBCC CHECKDB

automationdbcc-checkdblogssql servertrace

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:

enter image description here

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:
enter image description here

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.

Best Answer

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.

Related Question