THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Default value for OLE DB Destination FastLoadMaxInsertCommitSize in SQL Server 2008 [SSIS]

The FastLoadMaxInsertCommitSize property of the OLE DB Destination is used to determine how many rows should be committed as a single transaction when using the FastLoad option. In SQL Server Integration Services (SSIS) 2005 the default value for this property was zero which meant “regardless of the number of rows, commit all of them under a single transaction”. In SSIS 2008 that default value changed to 2147483647 meaning that SSIS would commit rows after approximately 2.15 billion and I was puzzled as to why so I asked the question on the SSIS forum on MSDN. Bob Bojanic, a developer on the SSIS team, replied with the following:

In SQL Server 2008, SQLServer Engine is acquiring a different set of locks than it used to do for SQL Server 2005. This change on the lock is creating a conflict in the SSIS pipeline causing an application deadlock. This was happening when packages used bulk-insert and update into the same table at the same time.

We changed the default value of this property to avoid the mentioned deadlock. There is also the upgrade code which changes the value of this property when SQL Server 2005 packages are upgraded.

So, now you know! Setting FastLoadMaxInsertCommitSize=2147483647 won’t affect you unless you’re inserting more than that many rows; if you ARE inserting more than 2147784647 rows then let me know because I’d love to hear about it :)

@JamieT

Published Saturday, March 06, 2010 10:12 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Siddharth Mehta said:

Jamie, Thanks for sharing this.

--Siddharth

March 6, 2010 11:02 AM
 

ihafidh said:

Jamie, thank you for researching this and sharing it.

March 8, 2010 10:39 AM
 

Adam Machanic said:

March 8, 2010 2:23 PM
 

Srinivas Parvathaneni said:

Good research and thanks for sharing this.

November 8, 2014 12:23 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement