SqlBulkCopy.BulkCopyTimeout property

c# .net sql-server-2008 bulkinsert

11,448

Solution 1

Searching and reading some blog postings and forum posts on the web appears to indicate the the SqlBulkCopyTimeout actually does apply not to the entire operation, but to each batch in the operation.

The MSDN docs isn't totally clear on this, but most posts seem to indicate that the timeout applies to the batch. Decreasing the batch size and/or increasing the SqlBulkCopyTimeout seems to be the solution to timeout problems in most cases.

See this forum post as an example.

Marc

Solution 2

BulkCopyTimeout: By default it is 30 second

sqlBulkCopy.BulkCopyTimeout = {time in seconds for one batch}

BatchSize: By default whole data source is one batch

sqlBulkCopy.BatchSize  = {no of rows you want to insert at once}

For more detail see this link:Timeout expired with SqlBulkCopy

We get this exception "Timeout expired." if application unable to insert the data source in 30 sec.

Solution 3

It applies to the entire operation for one batch. You have to make sure your connections timeout is set as well. I've hit the timeout before, set it on the bulk copy and found out the hard way that the connection time out matters also.

It seems that it's concept of entire operation is funny. If it ends up waiting for more than the timeout at any one time it will fail. In other words if the read of a batch takes more than the time out it will fail, but if the sum of all the reads is > timeout you're okay. If the write of a batch takes too long it will also fail.

But it seems to be batch by batch, not the whole thing.

11,448

Author by

George2

Updated on June 23, 2022

Comments

George2 almost 2 years

I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net to develop a console application to do bulk insert copy.

I want to use both bulk insert batch and bulk insert timeout property. For the BulkCopyTimeout property, I am confused and want to know whether it applies for the whole bulk or applies to only each batch of the bulk?

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.bulkcopytimeout.aspx

thanks in advance, George
George2 over 14 years

I set the timeout to 120 seconds. My whole bulk operation will last for 1 hour and each bulk batch takes about 20 seconds. I never experienced timeout issue. In your comments, I should experience timeout because 1 hour > 120 seconds?
George2 over 14 years

I set the timeout to 120 seconds. My whole bulk operation will last for 1 hour and each bulk batch takes about 20 seconds. I never experienced timeout issue. In your comments, I should experience timeout because 1 hour > 120 seconds?
George2 over 14 years

Mike, are there any official documents mention the meaning of BulkCopyTimeout applies to both each batch read and each batch write? Appreciate if you could provide a link. Thanks!
Mike Two over 14 years

@George2 not that I know of. I'm basing this on empirical evidence learned through hitting the timeout. Sorry that my first pass at the answer was misleading.
George2 over 14 years

Thanks! I am also interested in your comments -- "You have to make sure your connections timeout is set as well.". Could you explain how connection timeout impacts bulk copy please? My confusion is, once we connect to source/destination database, connection timeout will never take any effect. Connection timeout only charges how long it allows us to establish the connection. Please correct me if I am wrong.
George2 over 14 years

Thanks for your update, Marc! My further confusion is, bulk copy has two related phases for each batch -- (1) read from source and (2) write to desination. Do you think it means both (1) and (2) of each batch can not exceed BulkCopyTimeout? Or it means the sum of (1) and (2) of each batch can not exceed BulkCopyTimeout?
marc_s over 14 years

@George2: quite honestly: I don't know. The docs aren't very conclusive on this one, sorry.
marc_s over 14 years

Typically, though, if you cannot set the timeout for the read and write part separately, then the timeout applies to the whole operation (read+write)
George2 over 14 years

Thanks Marc, I am also interested in the connection timeout. Does the connection timeout impacts after we begin bulk copy? I think connection timeout only matters when we open the connection to database at the first time, once we established the connection, the connection timeout is no impact to bulk copy (read + write). BTW: connection timeout I mean msdn.microsoft.com/en-us/library/…
marc_s over 14 years

Connection Timeout only applies to the time it takes to establish the connection to the SQL Server. Once connected, it's no longer relevant.
George2 over 14 years

Thanks Marc, in ADO.Net, there is no idle connection timeout setting (idle I mean if no operation on the connection, the connection will timeout)?
marc_s over 14 years

@George2: no, once the connection is open, it remains open 'til you specifically close it.
George2 over 14 years

Thanks Marc, question answered!
Mike Two over 14 years

@George2 - you're right the connection time out behaves as you say. I was just remembering all the things I tried and not thinking about which ones actually helped.