What could cause a sleeping session to have an open transaction?
Scenarios that may explain these symptoms include:

The application code has started a transaction and not committed

The application SET IMPLICIT_TRANSACTIONS ON

The stored procedure includes SET IMPLICIT_TRANSACTIONS ON


This DMV query will help troubleshooting. The transaction name value will be &quot;implicit_transaction&quot; if started implicitly. Other possible values include &quot;user_transaction&quot;, &quot;DTC Transaction&quot;, or a user-specified transaction name. A &quot;user_transaction&quot; value (with no explict proc transaction) indicates the transaction must have been started on the client side and not committed. A value of &quot;DTC Transaction&quot; 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.