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.
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 &amp;gt; 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.