This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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