5

When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions while the query is in a sleeping status, sometimes for days (showing via sp_WhoIsActive). This leads me to believe that there is an error somewhere in the application that is not allowing the transactions to commit. What are all the ways that these transactions could possibly rollback?

  1. Client-side timeout (if specified)
  2. Client closes or restarts application
  3. Manual kill of the SPID
  4. Anything else?

Should I be doing anything with transactions that have been open for hours or days? If they are going to rollback or timeout at some point anyway, is there any harm in killing the process?

  • 1
    A deadlock can also cause one of the transactions to rollback. Although this is superficially related to your question but just FYI. – Shanky Nov 3 '15 at 7:15
7

To add to what @RLF provided in his answer; the following will also result in transaction rollback.

  1. ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE; will rollback any open transactions.

  2. Disconnection by the client, or server, or any bit of infrastructure in between.

  3. If the underlying disk subsystem goes away, all open connections to the database will be terminated. Uncommitted Transactions will be rolled-back when the database is subsequently opened.

  4. If the server is shutdown, or quits for any reason, uncommitted transactions will be rolled back when it starts up and opens the associated database.

  5. If SET XACT_ABORT ON is set, any T-SQL statement that raises a run-time error will cause an open transaction to roll back.

  6. Batch-scoped transactions, applicable to multiple active result sets (MARS), that have not been committed will roll back when a batch completes.

  7. For completeness, ROLLBACK TRANSACTION will, rather obviously, roll back a transaction.

  8. If the transaction log becomes full while a transaction is open, and the database is subsequently closed for any reason, roll-back will occur when the database is brought back online.

  9. If SET IMPLICIT_TRANSACTIONS ON; is set, including by ANSI default, uncommitted transactions will be automatically rolled back when the client disconnects.

4

For what it is worth, SLEEPING connections are usually representing a Client that has left a connection active, but is not doing anything. Therefore, the connection is sleeping.

Usually pretty harmless. But a SLEEPING connection that is holding an OPEN transaction can (but not necessarily) lead to problems with other transactions or with the transaction log.

Add to your list of problems: Network, Routing, and similar problems.

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.