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

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 *