SQL Server DBCC Commands: 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%';

Once you have the session id you can run the following:

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);

Need help with this or an of the other DBCC commands? The team at Stedman Solutions, LLC specializes in repairing corrupt databases and heping when things go wrong.

SQL Server DBCC Commands: Status of DBCC CHECKDB

Leave a Reply Cancel reply