What could cause a sleeping session to have an open transaction?

Question

For one of our SQL servers there has been a proc which creates a blocking mess:

That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from sp_whoisactive is status = sleeping and open tran 1 for duration of approx 5-6 minutes. In this duration heaving blocking chain shows up.

This SP do not have any transaction like BEGIN and END TRAN. It does some basic select col,col2,col3.... into #temptables from table1 inner join table 2 ... and then select from that #temptable

While we are checking from app why there could be a transaction left open, i was reading in such scenarios to use XACT ABORT ON in SP itself. But when there is no transaction involved how XACT ABORT setting will help in this case?

Please advise

Answer 1

Scenarios that may explain these symptoms include:

1. The application code has started a transaction and not committed

2. The application SET IMPLICIT_TRANSACTIONS ON

3. The stored procedure includes SET IMPLICIT_TRANSACTIONS ON

This DMV query will help troubleshooting. The transaction name value will be "implicit_transaction" if started implicitly. Other possible values include "user_transaction", "DTC Transaction", or a user-specified transaction name. A "user_transaction" value (with no explict proc transaction) indicates the transaction must have been started on the client side and not committed. A value of "DTC Transaction" indicates a distributed transaction, also indicating an issue with transaction management on the app side.

SELECT
      session_tran.session_id
    , active_tran.name 
FROM sys.dm_tran_session_transactions AS session_tran
INNER JOIN sys.dm_tran_active_transactions AS active_tran ON session_tran.transaction_id = active_tran.transaction_id;

It is a good practice to include SET XACT_ABORT ON in stored procedures with explict transactions to ensure transactions are rolled back, especially following a client timeout. That probably won't help here since your proc has no explict transaction, unless the proc happens to raise an error and rollback the erroneous transaction as result.