SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Orphaned transactions problems
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/10/2008 :  15:24:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 03/10/2008 :  21:48:47  Show Profile  Reply with Quote
Possible to restart sql to rollback it?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  05:46:21  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  06:14:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 03/11/2008 :  06:25:38  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  06:28:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What if you run
if @@trancount <> 0
	rollback



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  06:30:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  06:31:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://msdn2.microsoft.com/en-us/library/ms173730.aspx



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  06:33:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  06:56:00  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 03/11/2008 06:56:42
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  07:18:56  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  07:28:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  08:20:22  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/11/2008 :  08:31:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
But you can run other queries?

exec master..xp_cmdshell 'netstat'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  09:03:45  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  10:35:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  10:46:01  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/11/2008 :  15:43:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 03/11/2008 15:43:46
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/12/2008 :  15:46:02  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 03/12/2008 15:47:05
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35951 Posts

Posted - 03/12/2008 :  15:59:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/12/2008 :  16:03:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000