Sql-server – Sleeping sessions with an open transaction but empty input buffer

sql serversql-server-2016transaction

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?

Best Answer

This can sometimes happen when applications are using implicit transactions

It doesn't help that sp_who2 is really confusing -- you're better off with sp_WhoIsActive or sp_BlitzWho to find running queries.

One dead giveaway of implicit transactions is seeing the query text like begin tran or IF @@TRANCOUNT > 0.