SQL Server 2005 Query Analyzer: What happens when I stop a query before it finishes executing?
-
Hi, Quick question, I run an update statement to update a huge table then I click the Stop/Cancel button. What happens? Does it roll back all previous transactions or does it leave the updated rows as is? Thanks.
Rafferty
-
Hi, Quick question, I run an update statement to update a huge table then I click the Stop/Cancel button. What happens? Does it roll back all previous transactions or does it leave the updated rows as is? Thanks.
Rafferty
Its leave the updated rows. If you want to rollback all previous transactions, use the following before query: Begin Tran
-
Hi, Quick question, I run an update statement to update a huge table then I click the Stop/Cancel button. What happens? Does it roll back all previous transactions or does it leave the updated rows as is? Thanks.
Rafferty
-
First, I would say that it should be easy for you to test that yourself. Second, I just performed that test, and if the update is done by a single statement and that statement is canceled, it appears to be rolled back. Scott
Hi Scott, I did test it, and like you said it appears to be rolled back. Part of me just couldn't believe it so I asked here for more "documentation"-like answers :) But it does appear that if it's a single statement, the query analyzer will treat all the changes as part of one transaction. Thanks.
Rafferty
-
Hi Scott, I did test it, and like you said it appears to be rolled back. Part of me just couldn't believe it so I asked here for more "documentation"-like answers :) But it does appear that if it's a single statement, the query analyzer will treat all the changes as part of one transaction. Thanks.
Rafferty
Sorry, I didn't realize you had already tested it. At least our test results seem consistent. :) I did finally find this in the Books Online: Errors During Transaction Processing If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back. Scott
-
Sorry, I didn't realize you had already tested it. At least our test results seem consistent. :) I did finally find this in the Books Online: Errors During Transaction Processing If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back. Scott
Wow, there's the answer I'm looking for! Thanks Scott! :)
Rafferty