Check progress of DBCC CHECKDB

If you issue a DBCC CHECKDB on a big database to verify for consistency errors, it will take a long time to complete, but the Management Studio windows usually does not give you any hint about how long does it take, or a percentage progress. Luckily enough sql server has a Dynamic Management View that can solve your problem.

This is the SQL code to visualize progress of the operation

   1: SELECT  session_id ,

   2:         request_id ,

   3:         percent_complete ,

   4:         estimated_completion_time ,

   5:         DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime, 

   6:         start_time ,

   7:         status ,

   8:         command 

   9: FROM sys.dm_exec_requests

  10: WHERE database_id = 16

In my example I filtered the results only for the database and used the Id of the database that you can obtain with the DB_ID function.

An example of what you got with this query is represented in the following picture.

image

As you can see you can easily visualize percentage of completion, estimated end time and the command that is running.

Gian Maria.

Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

3 thoughts on “Check progress of DBCC CHECKDB”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.