Tuesday, October 30, 2018

How long will the CheckDB run?

For the most part, how long it will run depends on how large the database is, and how many objects are being checked.  More objects = more time.  There are also certain arguments that you can use to make it run faster and slower, like TABLOCK and PHYSICAL_ONLY respectively, but that's for another discussion.  In this post I just want to show you how to determine how long a checkdb is going to run before it is complete.

I ran the integrity check for the first time on a 1.7TB database over the weekend, and it ran for 23:12:44.  Pretty much a full day.  I knew it would be timely, but I wasn't expecting 24hrs.  I ran this statement about 16 hours into it, to find the estimated completion time:

       SELECT 
          session_id,
          request_id,
          percent_complete,
          estimated_completion_time,
          DATEADD(ms,estimated_completion_time,GETDATE()) [EstimatedEndTime],
          estimated_completion_time/(1000*60) [EstimatedCompletionTimeInMinutes],
          start_time,
          status,
          command
       FROM
          sys.dm_exec_requests
       WHERE
          session_id = 65 --< this is the spid for your actively running checkdb


And this was my output:

     

Not as clear as I'd like, but it told me I was about 69% complete and gave me the estimated end time -- both in minutes, as well as the actual estimated time it will complete.  In this case, it estimated 7:46AM, but it actually completed at about 7:02AM.  Because it was so long running, this information was very helpful.  I was able to reach out and let a few people know when it would be complete, rather than just saying 'I don't know'.

NOTE:  This doesn't just apply to the CHECKDB.  As per this reference,  the sys.dm_exec_requests DMV returns details about any request actively running on the server.  Can be quite helpful!




No comments:

Post a Comment