Imagine, you execute who_is_blocked stored procedure and get a list of the blocked resources, the databases involved, the requesting and the blocking session ids, and the request mode. With that information, you can choose to terminate the blocking session, or otherwise handle the issue… Thanks to T. Davidson who wrote the stored procedure below, it’s just that easy!
create proc dbo.who_is_blocked (@spid bigint=NULL)
— This stored procedure is provided “AS IS” with no warranties, and
— confers no rights.
— Use of included script samples are subject to the terms specified at
— T. Davidson
— This proc reports blocks
— 1. optional parameter @spid
‘blk object’ = t1.resource_associated_entity_id,
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)