Lets imagine that we use SQL Server Management Studio to execute a very long delete query. For the sake of this example lets say that it's execute time is 10 hours. While it executes a few hours into the process SSMS crashes. What will happen to the query? Will it be rolled back or will it continue executing despite the fact that SSMS is no longer running?
Database restores are terminated when SSMS crashes, but I'm curious to know what happens with alters and delets.
When SSMS closes for whatever reason, your session will end and any uncommitted transactions will be rolled back (and yup, database restores will stop).
BUT, if SSMS crashes and gives you the 'This Program Has Terminated Unexpectedly' or whatever the error message is, don't touch anything. At this point you might find that your session is still active and will be until you click 'Close' or 'Restart'.
I've had this a few times but if you open up another instance of SSMS and run sp_who2 you'll be able to see if your transaction is still active.
If your SSMS crashes, or you just close it, or just cancel your query, the connection is interrupred by client. There will be rollback done to your DELETE.
Sorry, I did not understand why database is restored. Server can do rollback without your intervention, but it never do restore if not explicitly asked to do restore.
