Orphaned transactions problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Orphaned transactions problems

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-10 : 15:24:26

so i'm having a bit of a bad day today

i have an app that starts a transaction (some deletes, etc...)
in the middle of this transaction the connection drops (say we pull out our network cable) but this will leave the transaction still running and it will be orphaned.
i know i can kill them in sql server, but what i'm looking for this to somehow not happen at all.
I tried SET XACT_ABORT ON but no luck.

Is what i need even possible?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-10 : 21:48:47

Possible to restart sql to rollback it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 05:46:21

no restart of the server is not possible.
or did you mean something else?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:14:35

quote:
Originally posted by spirit1

no restart of the server is not possible.

So restart is possible?

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-11 : 06:25:38

quote:
Originally posted by Peso

quote:
Originally posted by spirit1

no restart of the server is not possible.
So restart is possible?

E 12°55'05.25"
N 56°04'39.16"

Applying DeMorgan's theorem, Peter? Eh?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:28:05

What if you run

if @@trancount <> 0
	rollback

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:30:04

Use this link and scroll up to page 211

http://books.google.co.uk/books?id=fw9GyxBX0fkC&pg=PA212&lpg=PA212&dq=rollback+orphaned+transaction&source=web&ots=GllvLcyLib&sig=y3A3Az7cfYyo5eb-UZKR9sDIBy8&hl=en#PPA213,M1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:31:19

http://msdn2.microsoft.com/en-us/library/ms173730.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:33:07

quote:
Originally posted by Dan Guzman 02-Feb-08 09:10:22

Orphaned transaction in sql 2005

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).

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.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 06:56:00

yeah i've read that.

however i'm looking for a solution that wouldn't allow for this to happen at all... hopefully i'll find one...
i basicaly don't want to go to the server at all to handle this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 07:18:56

some more interesting info:

with this script i see that my delete sql is still in an open connection on port 3986


select	C.client_tcp_port, T.text
from	sys.dm_exec_connections  C
	CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
where	client_net_address = 'myComputerIpHere'

however running netstat on my computer doesn't show the connection is active on the port that the above sql returns.

select * from sys.dm_tran_locks also confirmes that my transaction is still running.

this is also confirmed by running dbcc page and seeing my deleted record (id = 100) being marked as ghost.
if i do a select with nolock on my table the record with id=100 isn't there.

this is weirding me out...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 07:28:52

What id you run NETSTAT on the server?
Has someone else access to your server on the port mentioned?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 08:20:22

haven't tried that... have to get telnet access to the server.
don't think so.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:31:58

But you can run other queries?


exec master..xp_cmdshell 'netstat'

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 09:03:45

tried that. have to enable it... doing other stuff now... i'll report back

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 10:35:08

well here's an update.

netstat on the server shows connection to my computer with the same data returned by the sql script in my previous post.
but running netstat on my computer still doesn't show that this connection exists.

so to where on my machine does this connection connect to???

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 10:46:01

just to let anyone who read the thread know that another issue has risen from this problem here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98716

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 15:43:18

also i've cleared the droped connection from connection pool with
SqlConnection.ClearPool(MyConn);
also perfom showed that the number of connections in the conn pool decreased right after this.
No change on the sql server side.

so to what is the server connecting to??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-12 : 15:46:02

well i found a kind of a hackish solution to this.
for each opened connection i remember its spid.
then if that connection suddenly gets disconnected and i get that nasty tcp transport failed error
i simply open the connection and kill the spid like this:


try
{
  string spid = "60";
  SqlCommand cmd1 = new SqlCommand("KILL " + spid, conn);
  cmd1.CommandType = CommandType.Text;
  int ok = cmd1.ExecuteNonQuery();
}
catch
{
// we leave this catch for the spid not found error and continue in our merry way
}

hackish but it works.

i guess another way is to change the TCP registry key on the sql server that's explained in this KB article:
http://support.microsoft.com/kb/137983/EN-US/

haven't tried that though

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-12 : 15:59:01

quote:

Requires membership in the sysadmin and processadmin fixed database roles.

You've given out this permission just to "fix" this?

Your transactions should be over in just a few seconds anyway (if they aren't then I'd be worrying about performance), so why does it matter if the spid is still connected?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-12 : 16:03:17

well they aren't.
sql server holds them open for 1 hour and with that x locks on the table as i explained at the begining of the topic.
any ideas on what the setting for this is?
i tried the "query governor cost limit" with sp_configure but that is set to default 0 which means unlimited time.
so why would it disconnect after exactly 1 hour.

we use sa for access so permissions aren't really important for this app.

i am open for other ideas though

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources