Status of DBCC CheckDB

So you are checking your database with DBCC CheckDB and of course if you are like me you use the WITH NO_INFOMSGS parameter. But it turns out that CheckDB is taking longer to run that you expected, and you want to check and see what table is currently being checked.

Here is a quick SQL Script that I use to find the current table that is being checked by DBCC CheckDB

use [your database];
go

-- use this to find the session id of DBCC CheckDB
SELECT session_id, start_time, command, percent_complete, total_elapsed_time,
       estimated_completion_time, database_id, user_id, last_wait_type
  FROM sys.dm_exec_requests
 CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 WHERE command like '%DBCC%';  

DECLARE @sessionID as integer = 53; -- enter the session ID of DBCC CheckDB Here

SELECT name, schema_id, type_desc
  FROM sys.objects
 WHERE object_id = (SELECT TOP 1 resource_associated_entity_id
                      FROM sys.dm_tran_locks
					 WHERE request_session_id = @sessionID
					   AND resource_type = 'OBJECT'
					   AND resource_associated_entity_id <> 50);



If the query returns nothing, that then refer back to the first query and confirm that DBCC is currently doing DBCC TABLE CHECK, as instead it may be doing DBCC ALLOC CHECK or another type of check. If you see DBCC ALLOC CHECK, wait a bit as the DBCC TABLE CHECK occurs after the ALLOC CHECK.
If you ran CheckDB in SSMS you don’t need to run the first query, you can just look at the name of the tab in SSMS to find the sessionID. Be sure to replace the hard coded 53 with the actual session ID that is running your DBCC CheckDB command.
From here you can get the name of the table being checked as the status of DBCC CheckDB.
Alternate Query
After writing the original query to check the status of DBCC CheckDB, I decided to update it to not have to copy and paste the session ID. Additionally I noticed the the query sometimes ran slow due to locking, so I added the WITH (NOLOCK) to avoid any locking. Normally I am not a big fan of NOLOCK, but this is the perfect example of the right time to use it.
USE [your database];
GO
DECLARE @sessionID as integer = 0;

SELECT @sessionId = session_id
  FROM sys.dm_exec_requests WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 WHERE command like '%DBCC%';  

SELECT name, schema_id, type_desc
  FROM sys.objects WITH (NOLOCK)
 WHERE object_id = (SELECT TOP 1 resource_associated_entity_id
                      FROM sys.dm_tran_locks WITH (NOLOCK)
					 WHERE request_session_id = @sessionID
					   AND resource_type = 'OBJECT'
					   AND resource_associated_entity_id <> 50);

How does this work?
DBCC CheckDB locks database resources when it check them, so scanning the object that is currently locked and associated with the session doing the check gives you the object that is locked. Keep in mind that if the check is being run against an index on a table, the table name will be returned, not the index.
Enjoy!
Related Links

Database Health Monitor


 


More from Stedman Solutions:



  

Steve and the team at Stedman Solutions are here for all your SQL Server needs.

Contact us today for your  free 30 minute consultation..

We are ready to help!