2

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:

enter image description here

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
Improve this question
5
  • 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. Commented Aug 20, 2015 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. Commented Aug 20, 2015 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? Commented Aug 20, 2015 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. Commented Aug 20, 2015 at 14:27
  • 1
    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? Commented Aug 20, 2015 at 14:46

2 Answers 2

Reset to default
3

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;
Improve this answer
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

Improve this answer
1
  • 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. Commented Aug 25, 2015 at 19:14

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.