Sleeping sessions

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

Parents

0 ilya.b 9 months ago

We had the same issue with that advisory condition, the results of that query are not consistent. So, I believe the query isn't correct. Not sure why it's filtering by [last_request_start_time], we would like to know when a transaction started, not a request.
I just modified it little bit:

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 dt.database_transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
ORDER BY dt.database_transaction_begin_time

Reply

0 ilya.b 9 months ago

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 dt.database_transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
ORDER BY dt.database_transaction_begin_time

Children

No Data