Orphaned transaction in sql 2005

Once in a while we end up with an orphaned transaction in sql 2005 around
the same application and it holds locks and the only way to get rid of it is
kill it.
So what causes it ? According to the app owner, they close the connection
and I have to say they do because the app is called so often and 99% of the
time it works fine and then once in 2 or 3 months, it leaves the orphaned
transaction.
The driver is a Java driver.. But I've seen this behavior on non Java
drivers as well.
So does SQL have any way of automatically killing orphaned transactions
Thank youRomeo,
The problem is that the SQL Server does not know it is an orphaned
transaction. If it knew, it would drop the transaction.
My anecdotal experience has been, if the machine that initiated the orphaned
transaction is logged off the domain, then logged back in the orphaned
transaction will ofter terminate. Apparently (real non-technical here) the
login back into the domain lets the SQL Server say to itself, "Hey, this
transaction must be orphaned because the machine I am doing it for just
logged in." But, I don't think I have ever seen this documented. (And it
is still a pain in the neck to track down the user and get them to log out,
then log back in.)
For orphaned DTC transactions, the SPID is -2, which you can find and kill.
This is documented in sp_who.
http://msdn2.microsoft.com/en-us/library/ms174313.aspx
RLF
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you|||> So does SQL have any way of automatically killing orphaned transactions
SQL Server will rollback an open transaction when the client disconnects so
I suspect that network connection is still open. Get the value of
client_tcp_port from sys,dm_exec_connections and check against a netstat
list on the client machine. If you see that the port is in use according to
netstat, then the connection is still open as far as the network layer and
SQL Server are concerned.
I don't know anything about your application other than you are using a java
driver with a SQL 2005 back end. Most drivers support some form of
connection pooling which means that a connection isn't actually closed when
the application closes the connection; the connection is simply returned to
the pool and is available for reuse. If a transaction is open when a pooled
connection is closed, it could remain open until the connection is either
reused or removed from the pool. The exact behavior depends on a number of
factors, such as the specific driver and how the transaction was initiated
(server vs. client API).
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
It may be than all is fine as long as the expected code path is executed.
Make sure that the application cleans up (e;g; WHILE @.@.TRANCOUNT > 0
ROLLBACK and close connection) following exceptions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you