Why are SELECT queries in sleeping status holding open transactions Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted transaction?).

Using the query below, I noticed that all of these open transactions are SELECT queries in a "sleeping" status. Why would a SELECT open a transaction? Could these be in the middle of a stored procedure somewhere, and the [TEXT] field is only showing the last statement in the procedure that was executed? There is no wait_info on any of the open transactions.

 SELECT [TEXT] as SQLcode 
 FROM SYS.SYSPROCESSES SP
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1
what about you start using this query below, and then tailor it for your own purposes:

select *
from sys.dm_tran_database_transactions t

join sys.dm_tran_session_transactions st on t.transaction_id = st.transaction_id 

join sys.dm_exec_sessions s on s.session_id = st.session_id 

join sys.dm_exec_connections c on c.session_id = s.session_id 

cross apply sys.dm_exec_sql_text(most_recent_sql_handle) 
where 1=1

and text &lt;&gt; 'begin tran' 

and text not like '%sp_MS%' 

and datediff(minute, last_request_end_time, getdate()) &gt; 60

and database_transaction_begin_time is not null


I use this one below, it helps in most cases:

    SELECT 
     es.session_id AS session_id

    ,CASE WHEN eS.LOGIN_NAME = eS.ORIGINAL_LOGIN_NAME 
     THEN eS.LOGIN_NAME 
     ELSE eS.LOGIN_NAME + ' (' + eS.ORIGINAL_LOGIN_NAME + ')' 
     END AS LOGIN_NAME

    ,es.host_name AS hostname
    ,es.status
    ,es.last_request_start_time
    ,es.last_request_end_time
    ,es.CPU_TIME AS CPU_TIME_MS
    ,es.MEMORY_USAGE AS MEMORY_USAGE_PAGES
    ,es.ROW_COUNT
    ,NULL AS blocked_by
    ,NULL AS waittype
    ,NULL AS waittime
    ,NULL AS lastwaittype
    ,NULL AS waitresource
    ,dbid=trans.database_name
    ,NULL as  cmd
    ,t.text AS [QUERY]
    ,NULL AS PARENT_QUERY

   ,TRANSACTION_ISOLATION_LEVEL = NULL

    ,NULL as cpu

    ,NULL AS physical_io

    --,es.open_transaction_count as [open_tran]
    ,es.program_name

    --,es.login_time
    --,ota.task_state
    --,ota.pending_io_count
    --,ota.pending_io_byte_count


    FROM sys.dm_exec_sessions AS es
    INNER JOIN sys.dm_exec_connections AS c
        ON es.session_id = c.session_id

    --LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id

    CROSS APPLY (

        SELECT MAX(DB_NAME(dt.database_id)) AS database_name
        FROM sys.dm_tran_session_transactions AS st
        INNER JOIN sys.dm_tran_database_transactions AS dt
            ON st.transaction_id = dt.transaction_id
        WHERE is_user_transaction = 1
        GROUP BY st.session_id
        HAVING es.session_id = st.session_id 

    ) AS trans

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
    WHERE es.session_id NOT IN (

            SELECT session_id
            FROM sys.dm_exec_requests

        )
        AND es.session_id IN (

            SELECT request_session_id
            FROM sys.dm_tran_locks
            WHERE request_status = 'GRANT'
        )
        AND STATUS = 'sleeping'
        AND is_user_process = 1
        AND es.session_id &lt;&gt; @@spid



  Please avoid using SYS.SYSPROCESSES