I have found out recently, that there are hanging several connections on our dev server (SQL Server 2016) with an open transaction. These transactions have been open for several days. I am using the query as follows:
SELECT session_id, se.database_id, status, open_transaction_count
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0
AND status = 'sleeping'
AND is_user_process = 1
It returns me several sessions with open_transaction_count=1. When I check the returned session_ids in the sys.dm_tran_session_transactions and sys.dm_tran_active_transactions the existence of these transactions is proved in these views. All of the transactions have transaction_state = 2.
I would like to see the last SQL statement or batch within these sessions to identify what code in our application causes it and that is the issues. When I use:
DBCC INPUTBUFFER([mySPID])
I will receive a query for some of the session_ids, but no result for the others. The same result is when I try the query as follows:
SELECT se.session_id, se.database_id, se.status, se.open_transaction_count, qry.text
FROM sys.dm_exec_sessions AS se
LEFT JOIN sys.dm_exec_connections AS con ON con.session_id = se.session_id
OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) AS qry
WHERE open_transaction_count > 0
AND status = 'sleeping'
AND is_user_process = 1
The interesting is that when I switch to the „problematic databases“ (those with no query text returned) and execute DBCC OPENTRAN the result is „No active open transaction“. I assume that it could be a select statement within an explicit unclosed transaction and so such a transaction is not returned with DBCC OPENTRAN. However, I am not sure with this interpretation.
I would like to know, why I cannot get the last statement/batch for these sessions and whether there is another way (then above mentioned) of how I can get it?
