When a workstation losts connection to server,
it can leave an uncomplete transaction. Then
SQL Server removes the transaction.
Could anyone guide me how to set the delay
before SQL Server do it ?

Thanks in advance
John S.



*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]

I'm not sure I understand your question - as far as I know, MSSQL will
rollback the transaction immediately, but why would you want to delay
the rollback? Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion.

Simon, thanks for the response.

[quoted text, click to view]
as far as I know, MSSQL will
rollback the transaction immediately, >>

It means : I want to know for sure how many seconds
is it from connection failure to the beginning
of rollback.

[quoted text, click to view]
the rollback? >>

No, I do not want to delay it.
If possible, I want to make the delay short,
as short as possible by adjusting any parameters
(like in Novell Netware : "number of watchdog -
packets", "delay between watchdog packets").

[quoted text, click to view]
to solve, someone will be able to make a suggestion. >>

Currently, I have no problem. It is only a question.
I also want to ask :
If I found that SQL Server took long to rollback
an uncommitted small transaction caused by a network
failure, what should I do ?

Regards
John S.




*** Sent via Developersdex http://www.developersdex.com ***

john smile (nospam) writes:
[quoted text, click to view]

The rollback sets in immediately as SQL Server notices that the client
is gone.

A more interesting question is maybe how long does it take from that
some pulls the plug for thw workstations, until SQL Server notices. As
far as I know, there is no configuration parameter for this in SQL
Server, and if this is controllable, I would guess it's more likely to
be on operating-system level. In my experience, the answer is close to
"immediately" here too.

[quoted text, click to view]

Take anóther cup of coffee. :-) There is not much you can do. Restarting
the server to force a recovery could save the say on SQL 6.5 if the
transaction was such that it blocked every thing else. This may be true
on SQL2000 too, but I don't know.

In any case, if it takes long time to roll back, it is not a small
transaction!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Erland,

Thank you very much for the answer.

Regards,
John S.



*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]

Design your transactions better.

Seriously, this is one of the issues we've looked at as we're moving to
clustering. Clustering basically involves shutting down and restarting SQL
Server, so any long transactions affect your failover time. And if for
example you had transactions that take 15 minutes to rollforward or back,
your failover will take at least that long to occur. Which sorta negates
the usefullness in most cases.

Generally small transactions won't take long at all to rollback. Even lots
of them.


[quoted text, click to view]