0

Is there any way to configure SQL Server 2008 to kill a transaction if it has neither been canceled nor committed for some time? (Say power, network connection or whatever gets cut to the computer having it open.)

Either so it happens automatically after some defined rule-sets or by making and calling a command line application that queries the SQL server for active transactions + time they have been running... and then instructs SQL Server to close those down that are "frozen".

Tom
  • 3,587
  • 9
  • 69
  • 124

1 Answers1

2

To quote Gail Shaw from here:

SQL Server does not time queries out, the connecting application (in this case query analyser) does.

Whichever tech that you're using to connect (ADO, etc.) will probably have a connection timeout and and execution timeout property that you can change in your calling code. Defaults are usually 30 secs.

You could potentially wrap something like this in a loop that kills each offending spid:

select datediff(second, last_batch, getdate()) as secs_running, *
from sys.sysprocesses
where hostname != ''
    and open_tran = 1

There would probably be many opinions on how to best find which processes are "safe" to kill, and I would certainly be a little worried about automatically doing such a thing based upon an arbitrary timespan. I'm also not sure that any data changes done in the process are guaranteed to be rolled-back.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • Thanks for your answer. I will keep it around as last resort :) – Tom Apr 12 '12 at 13:48
  • The changes will be rolled back, in the sense that any transaction that was in progress when it was killed will be rolled back. The linked question was doing many individual deletes without an encompassing transaction -- so the ones that were done already were unaffected. – Mark Sowul Aug 17 '15 at 16:13