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 2


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."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.