How to predict DBCC CHECKDB finish time

04 January,2016 by Jack Vamvas

Question: I’ve set off a dbcc checkdb command : DBCC CHECKDB () WITH NO_INFOMSGS, ALL_ERRORMSGS on a SQL Server database due to a unexpected server outage. It appears to be taking a longer than usual time. Is there a method I can use to view the percentage complete of the CHECKEB process?

Answer: Yes you can view the CHECKDB percent complete. Querying the sys.dm_exec_requests DMV reveals some useful information. This information includes elapsed time and Estimated Finish time.    To filter out this process from all the other SQL Server processes, you’ll need the session id

An example query to view the DBCC CHECKDB percent complete

SELECT session_id,percent_complete,DATEADD(MILLISECOND,estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sql_handle
FROM sys.dm_exec_requests where session_id = 62

Some sample output from the query

Dbcc_checkdb

Read More on DBCC CHECKDB

SQL Server – DBCC CHECKDB FAQ - SQL Server DBA

SQL Server - DBCC checktable - SQL Server DBA

List SQL Server DBCC commands using trace 2520 – documented and undocumented commands

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to predict DBCC CHECKDB finish time


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer