Post reply

DTS insert batch size vs. commit completed package

  • August 5, 2008 at 11:01 am

    #72778

    I am working with a DTS package that moves millions of rows. The options are set for insert batch size at 10000 but it is also set to use transactions and commit on successful package completion. Our transaction log is 2.5 GB and keeps filling up, hince, keeping the package from completing. However, some rows are being inserted and now we have to go back and delete these rows before we can run the package again. I have one person saying the package is wrapped into one transaction and that's why the log is filling up and I have another person saying because it's set to insert batch size 10000 its committing every 10000 rows and it must only be taking 10000 rows to fill the log. They're obviously conflicting options to be set on one package but which option would be over-ruling the other? What is the most likely cause for the log to be filling up?

  • August 5, 2008 at 11:33 am

    #853373

    Are you doing log backups during this process? Or are you in simple mode?

    The commits every xx rows doesn't prevent the logging or clear the log. Only a checkpoint (simple mode) or log backup (full mode) will clear the log space and allow it to be reused.

    Also, you should have a way to restart your package if it partially completes. Expecting a million row + load to succeed or fail as one unit can be problematic.

  • August 5, 2008 at 11:39 am

    #853379

    It is in simple mode. The issue is, for such a large insert it should be split up into separate transactions, the argument I'm getting is that because the insert batch size is set to 100000, it is being executed as separate transactions and should be doing a checkpoint after each batch. The other side of the argument is that because it is set to commit on package completion it is wrapped into a single transaction.

  • August 5, 2008 at 5:33 pm

    #853556

    I side with the second person. Insert batch size is the number of successful insertions between commits, so if it's set to 10,000, it will commit a transaction every 10,000 rows. These transactions are separate from the package transaction, of which there can be only one.

    Greg

Viewing 4 posts - 1 through 4 (of 4 total)

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