Transaction and Try-catch in SQL Server Job We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH and TRANSACTION blocks:

BEGIN TRY
    BEGIN TRANSACTION

        [[INSERT/update statements]] ...

    IF @@TRANCOUNT &gt; 0
    BEGIN
        COMMIT TRANSACTION
        PRINT 'Successful.'
    END

END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT &gt; 0
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Unsuccessful.'
    END
END CATCH


Does it ensure the data manipulations will be rolled back in case of error(s)? Or other considerations should be taken into account?

Would be any better way of doing that (using configurations, etc)?

Thank you.
I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount &gt; 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end


This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:


  Uncommittable Transactions and XACT_STATE
  If an error generated in a
  TRY block causes the state of the current transaction to be
  invalidated, the transaction is classified as an uncommittable
  transaction. An error that ordinarily ends a transaction outside a TRY
  block causes a transaction to enter an uncommittable state when the
  error occurs inside a TRY block. An uncommittable transaction can only
  perform read operations or a ROLLBACK TRANSACTION. The transaction
  cannot execute any Transact-SQL statements that would generate a write
  operation or a COMMIT TRANSACTION. The XACT_STATE function returns a
  value of -1 if a transaction has been classified as an uncommittable
  transaction. When a batch finishes, the Database Engine rolls back any
  active uncommittable transactions. If no error message was sent when
  the transaction entered an uncommittable state, when the batch
  finishes, an error message will be sent to the client application.
  This indicates that an uncommittable transaction was detected and
  rolled back.


Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie.  your catch should re-raise).