Howto find out details and reasons for idle transactions?

Question

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system:

Those bubbles are surrounded by a red border in case the transaction is idle. The legend says this is dangerous:

Those are some particular sample information related to spid 200:

spid           : 200
kpid           : 0
blocked        : 0
waittime       : 0
lastwaittype   : MISCELLANEOUS
waitresource   : 
dbid           : 5
database       : DBNAME
uid            : 1
cpu            : 0
physical_io    : 0
memusage       : 3
login_time     : 29.02.2016 09:37:17
last_batch     : 29.02.2016 09:37:17
ecid           : 0
open_tran      : 1
status         : SERVERNAME
program_name   : PROGRAMNAME
host_process   : 5380
cmd            : AWAITING COMMAND
nt_damain      : 
nt_username    : 
net_address    : 99887766
net_library    : TCP/IP
loginname      : sqlusername
stmt_start     : 0
stmt_end       : 0
input_buffer   : 

It's obvious it is dangerous. But I have trouble finding out the reason. Looking at the sys.sysprocesses DMV to find out the queries executed in the context of those spids. The queries executed are running very fast and are no candidates for long running. So I don't understand why they are getting idle within their transactions? How to find out the backgrounds?

EDIT: The query behind the scenes getting the processes is the following:

exec sp_executesql N'
            SELECT p.spid, p.kpid, p.blocked, CAST(p.waittime AS BIGINT) as waittime, p.lastwaittype, p.waitresource, p.dbid, db.name as [database], p.uid, CAST(p.cpu / 1000 AS BIGINT) as cpu, 
            CAST(p.physical_io AS BIGINT) as physical_io, p.memusage,
            p.login_time, p.last_batch , p.ecid , CAST(p.open_tran AS BIGINT) as open_tran, p.status, p.hostname, p.program_name, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address,
            p.net_library, p.loginame, p.stmt_start, p.stmt_end
            FROM master.dbo.sysprocesses p join master.dbo.sysdatabases db on p.[dbid] = db.[dbid]
            Where hostprocess <> '''' AND program_name <> @application',N'@application nvarchar(17)',@application=N'Idera.Application'

It looks like the OPEN_TRANS indicates processes in a transaction and having a red border. And the thickness of the red border increases over time meaning the longer this process is part of the resultset, the thicker the red border gets.

Answer 1

This is the query I am using to identify idle transactions holding locks.

SELECT 
    CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName
    ,s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
    ,CAST(s.context_info AS varchar(128)) AS [context_info]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.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 s.session_id = st.session_id 
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.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 program_name NOT LIKE '%SQLAgent - Job Manager%'
OPTION (RECOMPILE, FORCE ORDER);

The reson why they are dangerous is simple: they are holding locks but doing nothing, waiting for the application to either commit or rollback the transaction.

This typically happens when the application performs some heavy task on the client side, without interacting with the database, still keeping transactions open. The worst scenario is applications showing error messages or waiting for user input during a transaction.

If you want to know what happens, you need to hunt down these applications and understand what they are doing while keeping transactions open.