I have a SQL SP which has a set a transaction. All the statements handled inside the TRY CATCH block. If we explicitly cancel the job by close the button in the JOb execution window, will it get into the catch block and automaticall rollback all the transaction tried in the TRY block ?
2 Answers
No, it probably won't. I can't say for sure, but client "aborts" normally don't rollback if the connection is returned to the connection pool
Add SET XACT_ABORT ON to your code or stored procedure
See (SO) https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 and SQL Server Transaction Timeout
I would say no. Assuming that pressing close button sends "stop notification" then here is what msdn says:
"When SQL Server Agent receives the stop notification, it waits for the job step that is currently running to finish before it processes the cancel request. Some long-running Transact-SQL statements such as BACKUP, RESTORE, and some DBCC commands can take a long time to finish."
