DBCC checkDB – A Nice trick to see progress

I hate the situation where I have started a DBCC CHECKDB and I don’t know for how long time it has been running. Well, I have created this very small T-SQL statement that you can run.

select session_idcommandpercent_complete

from sys.dm_exec_requests

where session_id=55


Session_ID is the session that has started the dbcc command. The result looks like the picture below.
 
Skærmbillede 2013 04 07 kl 09 20 13
 
Execute it continuously and see that percent_complete will grow.
 
It is important to know that DBCC CHECKDB executes three statements:
 
  • DBCC CHECKALLOC
  • DBCC CHECKTABLE
  • DBCC CHECKATALOG
So actually the percentage will have to go to 100% three times. When the line disappears, then it is finished.
 
One other thing to note is that DBCC is using space, and to see how much space is required to run a dbcc CHECKDB, then simply write
 

dbcc checkdb(‘adventureworks2012’with Estimateonly


Change adventure works to your database.  On my server, the result is:


Skærmbillede 2013 04 07 kl 11 23 15

 

 

In my case DBCC CHECKDB requires 336 KB in TEMPDB.
 
 
 

2 comments

  1. Hi nerosdk71 , We ran the TSQL statement that you provide us in your blog in order to know the percentage of completation and the result was so awesome , We could to provide visibilty to our client of the DB check completation time.

    We have a doubt with other command ( KILL 52 WITH STATUSONLY), Do you know if is safe use this command?
    I mean , Until where I know this command just execute an display the ROLLBACK average and completation time of the ROLLBACK process.

    Could you please to comment something?

    Thanks so much.
    Ramiro Gonzalez
    ramirovite@hotmail.com
    SAP Basis Consultant

    Like

    1. I think it is safe, and if you look in documentation, then you will see:

      “Generates a progress report about a specified session ID or UOW that is being rolled back due to an earlier KILL statement. KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.”

      Please note that it says DOES NOT TERMINATE OR ROLLBACK.

      to be sure I ran this in a window in management studio

      waitfor time ’10:00:00′

      I saw the session_id at top of the page and in another window I wrote

      kill 55 with statusonly

      (55 is the session_id).
      I got this errormessage:

      Status report cannot be obtained. Rollback operation for Process ID 55 is not in progress.

      BUT the session continued to run.

      ANOTHER POINT! this only applies when you use with statusonly. without statusonly, then you should be very careful about using kill. I have tried it at a customer with VERY Large databases, and the rollback took more than one day!!!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s