how to find the T-SQL of a sleeping session that is blocking some other process?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --DROP PROCEDURE dbo.sp_radhe GO CREATE PROCEDURE dbo.sp_radhe AS /* ======================================================================= Script : SP_RADHE Author : Marcelo Miorelli Date : 04 MAR 2013 Wednesday Desc : shows the current processes Usage : sp_radhe -- same as sp_who2 ======================================================================= History Date Action User Desc ----------------------------------------------------------------------- 27-oct-2014 changed Marcelo Miorelli commented out the line --and es.status = 'running' so the procedure returns any es.status ======================================================================= */ --====================================== -- describe primary blocks of processing --====================================== ------------------------------------------------ -- describe action of logical groups of commands ------------------------------------------------ -- describe individual actions within a command set BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid --and es.status = 'running' GO exec sys.sp_MS_marksystemobject 'sp_radhe' GO However, when a process is being blocked by some other process that is not active, I am struggling to figure it out the T-SQL of the blocking code. For example: the session 92 you can see on the above picture is a select and the session 75 is an update that I left the transaction open. **session 92** SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT TOP 1000 [accountID] ,[accountCreateKeyID] ,[totalAccountCreditValueLocal] ,[accountCreateDate] ,[createdDate] ,[createdBy] ,[modifiedDate] ,[modifiedBy] FROM [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] WITH (HOLDLOCK) **session 75** BEGIN TRANSACTION T1 SELECT @@TRANCOUNT update [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] set [totalAccountCreditValueLocal] = 1000 where accountID = 1 How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'? **New version** this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session. SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid UNION SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid