Sleeping sessions

Malraff

hi folks

just purchased sentry one, and trying to action some of the issues it is bringing to my attention 

a daily occurrence is high volumes of "sleeping sessions with old open transactions"

the query used to get these details is 

SELECT TxDesc = '[' + CONVERT(varchar, es.last_request_start_time, 120) + '] (' + CAST(es.session_id AS varchar(6)) + ') ' + host_name + ':' + program_name + ' [' + DB_NAME(dt.database_id) + ']'
	, OpenMinutes = DATEDIFF(minute, es.last_request_start_time, GETDATE())
FROM sys.dm_exec_sessions es
JOIN sys.dm_tran_session_transactions st
  ON es.session_id = st.session_id
JOIN sys.dm_tran_database_transactions dt
  ON dt.transaction_id = st.transaction_id
WHERE dt.database_id <> 32767
  AND status = 'sleeping'
  AND es.last_request_start_time < DATEADD(MINUTE, -5, GETDATE())
ORDER BY es.last_request_start_time

when i run this in sql, it will give me something like 

TxDesc                                                                                                                      OpenMinutes
[2022-03-27 08:46:51] (207) E10APP01:.Net SqlClient Data Provider [EpicorERP]       1427
[2022-03-27 08:46:51] (207) E10APP01:.Net SqlClient Data Provider [tempdb]            1427

but when i run the query again, it will have zero results - i can run five/6 times with zero results, then the above will appear again with the open minutes incremented accordingly

any idea why does the query sometimes produce no results?

thanks

Mal

EitanBlumin

Hi,

We developed our own variation of this condition. Its query looks something like this:

SELECT 
TxDesc = 'SPID ' + QUOTENAME(CAST(s.spid AS varchar(6))) + ' is sleeping since ' + QUOTENAME(CONVERT(VARCHAR(19), s.last_batch, 120))
+ ' with open transaction(s). hostname: ' + QUOTENAME(RTRIM(s.hostname)) + ', IP: ' + ISNULL(QUOTENAME(c.client_net_address), '(unknown)')
+ ', program: ' + QUOTENAME(RTRIM(s.program_name)) + ', database: ' + QUOTENAME(DB_NAME(s.dbid))
+ CASE WHEN EXISTS ( SELECT 1
FROM sys.dm_tran_active_transactions AS tat
JOIN sys.dm_tran_session_transactions AS tst
ON tst.transaction_id = tat.transaction_id
WHERE tat.name = 'implicit_transaction'
AND s.spid = tst.session_id
) THEN N' (IMPLICIT TRANSACTION)'
ELSE N''
END
+ ISNULL(', last command executed:

' + LTRIM(RTRIM((SELECT TOP 1 [text] FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))))
, '')
, OpenMinutes = DATEDIFF(minute, s.last_batch, GETDATE())
FROM sys.sysprocesses s
INNER JOIN sys.dm_exec_connections c ON s.spid = c.session_id
WHERE s.dbid <> 32767
AND s.status = 'sleeping'
AND s.open_tran > 0
AND s.last_batch < DATEADD(MINUTE, -5, SYSDATETIME())
AND EXISTS (SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = s.spid
AND NOT (resource_type = N'DATABASE' AND request_mode = N'S' AND request_status = N'GRANT' AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE'))
ORDER BY s.last_batch ASC
OPTION(MAXDOP 1);