April 24, 2009 at 9:28 am
Say you begin a transaction and it dosen't have a chance to commit before your network connection is dropped, what is the behavior that SQL Server should do with that transaction? I'm looking for good options to handle transactions on a flaky network.
April 24, 2009 at 9:37 am
The server will timeout the connection and rollback the transaction.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 9:46 am
That is set on the server for 600 seconds for "remote queries" under the Server Connections. So then the connection is left sort of in an "orphaned" state, pardon the poor choice of terms until it times out? Is there a way to re-establish the same connection?
April 24, 2009 at 9:57 am
The question is. Do you manage the transactions inside the SQL code or from the client?
You can not get that connection back. If you issued a command and the SQL is actively communicating with the client it will know that the connection went belly up because it will not be able to confirm the SQL code finished. I think the timeout is for non active connections.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 10:04 am
The transactions are handled by the client side.
April 24, 2009 at 10:06 am
If I read your post correctly the 'remote query' timeout is not what we are talking about here. The timeout you mention is used by the SQL Server itself to cancel any connections the Server initiated on remote servers.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 10:11 am
ah, my mistake, thank you. So I'm assuming there is a timeout setting in SQL Server then for connections that were dropped to clean them up?
April 24, 2009 at 11:05 am
Yes, but I do not remember what setting it is and if you can control it.
I think there are two separate mechanisms for detecting broken connections.
1. If you issue a SQL command, the Server will try to let you know when it is done with it. If it can not communicate back with the client it will kill that connection.
2. If you a connection open but do not issue any specific commands for a while - the server is going to ping that connection to make sure it did not go offline. I do not remember exactly but this timeout is around 15 minutes or so. The last time I had to deal with this issue was SQL Server 7 so things might have changed since.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply