1

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.

Marcello Miorelli
  • 14,544
  • 46
  • 126
  • 249

2 Answers2

4

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.

LowlyDBA - John M
  • 10,812
  • 11
  • 39
  • 59
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

LowlyDBA - John M
  • 10,812
  • 11
  • 39
  • 59
AdamRidgway
  • 21
  • 4
  • This does not work for me. I ran DBCC check 3 hours ago, and this returns nothing. I think it only works if there is an error.. – James Jenkins Sep 30 '19 at 15:27
  • @JamesJenkins if you run `dbcc checkdb` with `WITH NO_INFOMSGS` parameter it does not write to the log. – Marcello Miorelli Oct 01 '19 at 15:07
  • 1
    I figured out what happened. on my test system I had cycled the logs a minute after I completed the DBCC checkdb, This code is only checking the active log file. I ran `DBCC checkdb` a second time and this code works great. – James Jenkins Oct 01 '19 at 15:47