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)


 


 

Comments (0)