3

I have a procedure that allows me to see what is currently running in my sql server.

This procedure does not use sys.sysprocess because it has been deprecated.

However, I am struggling to find needed information about sleeping processes that are causing blocking.

This is because the DMV sys.dm_exec_requests does not hold any request of sleeping processes.

I don't want to use sp_whoisactive or any monitoring tool. sp_whoisactive uses sys.sysprocesses.

I have my session 81:

 BEGIN TRANSACTION T1 SELECT @@TRANCOUNT update [TableBackups].[dbo].[spstats] set execution_count = 1000 where dbname = 'master' select @@spid

and I have my session 51:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT TOP 1000 * FROM [TableBackups].[dbo].[spstats] WITH (HOLDLOCK)

These 2 sessions above are running at the same time and consequently, session 81 is blocking the session 51.

I have this select below, that get information about the session 81, however, I could not get teh dbid, and I am not sure about the Open_transactions and the whole waits information.

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 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

The question is, is there any DMV, other than sys.sysprocesses that I can use to get this info?

Improve this question

1 Answer 1

Reset to default
5

This is what I use to monitor sleeping SPIDs causing blocking issues:

SELECT s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id CROSS APPLY ( SELECT MAX(DB_NAME(dt.database_id)) AS database_name FROM sys.dm_tran_session_transactions AS st INNER JOIN sys.dm_tran_database_transactions AS dt ON st.transaction_id = dt.transaction_id WHERE is_user_transaction = 1 GROUP BY st.session_id HAVING s.session_id = st.session_id ) AS trans CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t WHERE s.session_id NOT IN ( SELECT session_id FROM sys.dm_exec_requests ) AND s.session_id IN ( SELECT request_session_id FROM sys.dm_tran_locks WHERE request_status = 'GRANT' ) AND STATUS = 'sleeping' AND is_user_process = 1;

Hope this helps.

To answer your question specifically, sys.dm_tran_database_transactions holds the information you're looking for.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.