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:


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?


1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.