Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question

migrated from Feb 27 '12 at 14:52

This question came from our site for professional and enthusiast programmers.

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) and SQL Server Transaction Timeout

share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.