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

Reply
  • 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);

Children
No Data