Post reply

Transaction Logs(.ldf) size increasing upto 50 GB

  • March 24, 2009 at 8:34 am

    #965259

    Whoo - that's big! Thanks for the code. I can understand the transaction log size now!

    OK, basically a transaction is there to make sure that some work either ALL happens (commitment) or NONE of it happens (rollback).

    So to give an example if I want to create a bank account and it needs entries in three tables, I would

    BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO A (whatever)

    INSERT INTO B (whatever)

    INSERT INTO C (whatever)

    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH --TRAN_ABORT

    ROLLBACK

    END CATCH

    Now the thing about this is that as serinor.e090266 posted earlier, these transactions are usually kept quite small for lots of different reasons.

    Taking the transaction out would be a design issue. It's not usual to have transactions around such a big, long running piece of code.

    If your job is clearing down some tables and repopulating them, you might decide not to use transactions because if the job fails you would just rerun it.

    If you did this it might be a good idea to clear the tables in the CATCH block at the end if the job fails, to avoid people looking at potentially incorrect results.

    So, to summarise, consider removing the transactions completely but understand that this might mean you have to add extra error handling of your own.

    Hope this helps.

    .

  • March 24, 2009 at 8:38 am

    #965263

    OR alternatively if your question was whether this was normal and it's not really a problem having such a large LDF, then yes it is normal for your code and you don't need to change anything!

    .

Viewing 2 posts - 16 through 16 (of 16 total)

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