Cancel SQL Job execution make automatic rollback 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 ?
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