1

What happens if a stored procedure is run in sql server management studio (SSMS 2012 to 2005 server) and it crashes?

I'm guessing the stored procedure continues to run?

On the other hand I have read somewhere that if a connection is lost and a transaction is used, then the transaction automatically rolls back?

Alex KeySmith
  • 721
  • 2
  • 10
  • 19

1 Answers1

3

If connection is lost, your session will be closed and all transactions will be rolled back. If you want to run your procedure without interruption, you could execute it as a job.

ibykov
  • 53
  • 4
  • Thanks @ibykov, so I assuming if there is no transaction then it continues on as normal? Or does it kill the process? – Alex KeySmith Mar 20 '13 at 17:00
  • 1
    No, it kills the process. But if you run in auto commit mode, all changes procedure made **before the crash** will be saved in database. – ibykov Mar 20 '13 at 17:12
  • 1
    It is also possible that procedure will be executed to the end, because Sql Server doesn't kill sessions immediately. – ibykov Mar 20 '13 at 17:22