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 replies
  1. Ramiro
    Ramiro says:

    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

    Reply
    • nerosdk71
      nerosdk71 says:

      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!!!

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *