3

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

6

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.

| improve this answer | |
  • Thanks Dan, Will a TRY/CATCH help here to implement if abort setting makes not much sense – BeginnerDBA Jul 10 at 18:25
  • You could include a CATCH block like IF @@TRANCOUNT > 0 ROLLBACK; THROW;, with or without SET XACT_ABORT ON;, to rollback a transaction even if not start by the proc. However, that will not help unless an errors occurs during execution. Also, the CATCH block will not be executed if the client cancels the query due to a timeout. – Dan Guzman Jul 12 at 12:49
  • thanks. Is there a way to query from SQL server if store procs coming from app are using which option of implicit transaction oN or off – BeginnerDBA Jul 14 at 19:33
  • 1
    @BeginnerDBA, I added a query to my answer that should help. – Dan Guzman Jul 15 at 10:57
  • 1
    @BeginnerDBA, to get the last batch executed, add input_buffer.event_info to the column list and this to the end of the query: OUTER APPLY sys.dm_exec_input_buffer(session_tran.session_id, NULL) AS input_buffer – Dan Guzman Jul 15 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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