SYSK 261: Investigating Blocked Processes in SQL Server

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)