Howto find out details and reasons for idle transactions?
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.