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

Question

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

You can see the last batch submitted using DBCC INPUTBUFFER(75); - this may or may not be useful, depending on the command(s) and nest level. — Aaron Bertrand, Aug 20 '15 at 14:02
I was thinking about a DMV so that I could join it in, I am trying to avoid DBCC also. I am having a look at sys.sysprocesses at the moment. — Marcello Miorelli, Aug 20 '15 at 14:05
Don't use sys.sysprocesses, it's deprecated. And why do you need to join? Are you trying to troubleshoot an issue, or make a pretty report? — Aaron Bertrand, Aug 20 '15 at 14:23
I actually have a procedure that I use in all my servers, just I noticed that when there is a process blocking another process, if the blocking process is suspended or sleeping it does not show on the results, and I would like to see what is blocking. I will update my question to clarify, I will add my procedure. — Marcello Miorelli, Aug 20 '15 at 14:27
Instead of reinventing wheels that others have already solved, have you tried Adam Machanic's sp_WhoIsActive, or considered a 3rd party monitoring tool that doesn't require you to catch these things in the act? — Aaron Bertrand, Aug 20 '15 at 14:46

score 4 · Answer 1 · edited May 13 '20 at 19:07

4

You can use Adam Machanic's SP_WHOISACTIVE to get the details tracked.

One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. This usage is documented.

Please see on how to collect the data for same here :Collecting Data from sp_WhoIsActive in a Table

edited May 13 '20 at 19:07

Aaron Bertrand

180,303
28
400
614

answered Aug 20 '15 at 14:46

KASQLDBA

7,159
6
27
53

2

To supplement this answer, make sure you use @show_sleeping_spids = 1 and @find_block_leaders = 1 when using sp_whoIsActive to get complete blocking details. – Kin Shah Aug 25 '15 at 19:14

score 3 · Accepted Answer · answered Apr 03 '16 at 12:11

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;

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

2 Answers2

Linked

Related