Best Practice for batch processing In SQL Server

  • Hello,
     I have a general question regarding best practices for creating a batch process in SQL Server 2016. 
    All code is residing in the database procedures. I need to make sure that there is a easy way to restart the process in case of failure. A typical batch process will insert/Update multiple rows in multiple tables. In case of a failure how to roll back and restart? Since this is a very complex batch, it is not one big unit where I can have all the code within one begin/end transaction.
    My first thought was to take a backup of all the tables that will be affected by this batch. Incase of failure data can be restored from backup and batch can be restarted. There may be 5 or 6 tables that are growing and the upper limit could be half a million records or so in each of the tables.
    Is there a better way to implement this process?

    Thank you for your valuable time and input.

    Regards,
    MMuthu

  • Miru Seshadri - Wednesday, December 19, 2018 8:15 AM

    Hello,
     I have a general question regarding best practices for creating a batch process in SQL Server 2016. 
    All code is residing in the database procedures. I need to make sure that there is a easy way to restart the process in case of failure. A typical batch process will insert/Update multiple rows in multiple tables. In case of a failure how to roll back and restart? Since this is a very complex batch, it is not one big unit where I can have all the code within one begin/end transaction.
    My first thought was to take a backup of all the tables that will be affected by this batch. Incase of failure data can be restored from backup and batch can be restarted. There may be 5 or 6 tables that are growing and the upper limit could be half a million records or so in each of the tables.
    Is there a better way to implement this process?

    Thank you for your valuable time and input.

    Regards,
    MMuthu

    Is your process the only process which modifies these tables?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Yes, at the time of modification. It is a batch and will be run during off hours.
    Thanks
    MMuthu

  • Preprocess all your data in staging tables.  When you're ready to update the final tables from the staging tables, start with ...

    SET XACT_ABORT ON;
    BEGIN TRANSACTION

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Jeff. I will try that.
    What is the difference between Xact Abort and Rollback transaction? Currently, I have all my data processing code in a Try block with a begin transaction. In the catch block, I have a rollback transaction. Would that be different than XACT ABORT ?
    Thank you.
    MMuthu

  • Miru Seshadri - Wednesday, December 26, 2018 5:50 AM

    Thank you Jeff. I will try that.
    What is the difference between Xact Abort and Rollback transaction? Currently, I have all my data processing code in a Try block with a begin transaction. In the catch block, I have a rollback transaction. Would that be different than XACT ABORT ?
    Thank you.
    MMuthu

    XACT_ABORT auto-magically does the rollback and forces discontinuation of the code in multi-statement transactions.  You still need the rollback check in the CATCH block to ensure that there's no transaction still in-process, just in case.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply