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
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!
Thanks for sharing it Steve. It would be useful while monitoring checkdb process execution.
Great stuff Steve. You’d think by now Ms Sql would have of this sort of thing standard in the SSMS.
That would be nice… but unfortunately they don’t.
In our case it’s running in parallel, so only showing table sysschobjs and wait event as CXPACKET. Running for more than 4/5 hours.
Curious why would this return a database_id number that doesn’t exist and doesn’t match the database that has DBCC CHECKDB running on it?
the schema_id is not a database_id, is is the schema like sys or dbo or something custom. If you replace schema_id with schema_Name(schema_id) then you will see the schema name.
-Steve Stedman