Most of us want to know how long it takes to run out CHECKDBs. If we ever run into corruption or worse yet a DR. your going to be running CHECKDB and knowing how long it takes is a lot better for you when the boss comes a calling wanting to know when your going to have that VIP database back up and running.

 

If you use Ola Hallengren (and a whole bunch of us do! thank you Ola! http://ola.hallengren.com/) then the DatabaseIntegrityCheck – USER_DATABASES/ DatabaseIntegrityCheck – SYSTEM_DATABASES maintenance jobs (Or any custom jobs you add using the DatabaseIntegrityCheck SP) pumps the results out to the CommandLog table (default installs puts the stored procedures and the CommandLog table in Master).We can then query the table and put the results into a report or SSMS grid view to look at how long things are taking. Below is my query to get details off all databases (including system dbs) and pivot the last 7 days.

 

WITH    rankcte ( [ORDER], databasename, [duration] )
          AS ( SELECT   RANK() OVER ( PARTITION BY DatabaseName ORDER BY StartTime DESC ) AS 'order' ,
                        [DatabaseName] ,
                        DATEDIFF(SECOND, [StartTime], [EndTime]) AS 'duration'
               FROM     [InstanceState].[dbo].[CommandLog]
               WHERE    Command LIKE '%DBCC CHECKDB%'
                        AND StartTime > GETDATE() - 7
             )
    SELECT  *
    FROM    rankcte PIVOT
( SUM([duration]) FOR [ORDER] IN ( [1], [2], [3], [4], [5], [6], [7] ) ) AS resultTable;

 

The output looks like:

integritiyCheckResults

Advertisements