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)

as

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

-- http://www.microsoft.com/info/cpyright.htm

--

-- T. Davidson

-- This proc reports blocks

--    1. optional parameter @spid 

--

 

select 

    t1.resource_type,

    'database'=db_name(resource_database_id),

    'blk object' = t1.resource_associated_entity_id,

    t1.request_mode,

    t1.request_session_id,

    t2.blocking_session_id     

from 

    sys.dm_tran_locks as t1, 

    sys.dm_os_waiting_tasks as t2

where 

    t1.lock_owner_address = t2.resource_address and

    t1.request_session_id = isnull(@spid,t1.request_session_id)