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

Children
No Data