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?
- Client-side timeout (if specified)
- Client closes or restarts application
- Manual kill of the SPID
- 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?