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

  • 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:<br/>
    ' + 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);

  • Hello,

    My guess is that it has something to do with a SPIDs Status or Start Date filters.

    The query itself only returns transactions older than 5 minutes by default, to avoid bringing back unnecessary results on systems with many short-running transactions, and has to be in a sleeping state.

    The intent being to detect when there are sleeping sessions with open transactions older than 10 minutes by default. Such sessions can cause blocking, and can prevent the transaction log from clearing, leading to excessive log file growth and space exhaustion. Additionally, when snapshot isolation is used, they can prevent version cleanup from occurring in tempdb.