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
