Sql-server – 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

Best Answer

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.

You can use

SELECT db_name(S.database_id) AS DatabaseName,
        ST.text
FROM   sys.dm_exec_connections AS C
       JOIN sys.dm_exec_sessions AS S ON S.session_id = C.session_id
       OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE  C.session_id = 75;

Best Answer

Related Solutions

Sql-server – How to find out the account(s) under which the sql server is running

Sql-server – how to get the information about sleeping processes that are causing blocking

Related Question