A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


sp_blocked - procedure for viewing locks and blocks

create procedure sp_blocked
as
begin
 
       SELECT *
       FROM sys.dm_exec_requests
       WHERE blocking_session_id <> 0;
 
       SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
       FROM sys.dm_os_waiting_tasks
       WHERE blocking_session_id <> 0
 
       SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id
       , resource_subtype, resource_description, request_status, request_owner_type, request_mode
       FROM sys.dm_tran_locks
       WHERE resource_type IN ('PAGE', 'KEY', 'EXTENT', 'RID');
 
       SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName
       , tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
       FROM sys.dm_tran_locks as tl
       INNER JOIN sys.dm_os_waiting_tasks as wt
       ON tl.lock_owner_address = wt.resource_address;
 
end

 

Add comment