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.

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.

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

  • 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 at 15:27
  • @JamesJenkins if you run dbcc checkdb with WITH NO_INFOMSGS parameter it does not write to the log. – Marcello Miorelli Oct 1 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 1 at 15:47

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.