SSIS Blowing Up Your Log?
If you have an SSIS package that is causing your transaction log to explode, one of the first places to look would be at the ‘FastLoadMaxInsertCommitSize’ property (this assumes you are using an OLEDB destination). As its name suggests, this property determines the size of batches that SSIS will contain within a transaction before committing. I.e., if you set this to 100,000, SSIS will attempt to insert your data in batches of 100,000 rows, with each being contained within it’s own transaction.
By default this is set to zero, which tells SSIS to attempt to contain the entire bulk insert within one transaction. In my case, that meant over ten million rows! Needless to say, my 2GB transaction log filled up in no time. By changing this value to a more manageable 1,000 rows, the package completed without issue.
Note that in some cases containing your operations within a transaction is a good thing. In this case, it was not necessary, as the package is performing a simple bulk transfer to a reporting database, no atomicity is not a concern.
