Timeout expired with SqlBulkCopy

Question

I'm using SqlBulkCopy to restore tables from xml backups. One of the table backup is ~200MB large and has a lot of records.

I'm having error:

Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding.

Abdul Saboor · Answer 1 · 2015-08-17T12:59:47.670

45

There are two ways to fix this error:

Increase Timeout by default it is 30 second and 0 means infinite.
```
   sqlBulkCopy.BulkCopyTimeout = {time in seconds}
```

Decrease BatchSize by default it try to insert all rows in one batch

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

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

edited Aug 17 '15 at 12:59

answered Oct 18 '12 at 11:34

Abdul Saboor

4,079
2
33
25

7

See also: "What is the recommended batch size for SqlBulkCopy?" http://stackoverflow.com/questions/779690/what-is-the-recommended-batch-size-for-sqlbulkcopy – Mark Byers Jan 11 '16 at 12:16
2

Best answer is to set the default batch to use smaller chunks of data. Why? One really good reason is that if you have 45 million rows and you set the timeout to infinite, you will wait a really long time to finally run out of memory and crash the process which will result in zero rows inserted and a lot of frustration plus a really long test/dev cycle. I used a small batch of 100 rows without adjusting the timeout at all and hit three different (unrelated) errors. I was able to fail fast, make corrections and after each failure I had data in my table to test with. – Vance McCorkle May 19 '20 at 00:04

score 45 · Accepted Answer · answered Dec 26 '10 at 21:49

45

You probably need to increase the timeout. Try increasing the value of sqlBulkCopy.BulkCopyTimeout from the default which is 30 seconds.

answered Dec 26 '10 at 21:49

Mark Byers

811,555
193
1,581
1,452

13

A TimeOut from 0 means infinite. – Tim Schmelter Dec 26 '10 at 21:56
5

Thanks for the solution. I've also found and tested reducing sqlBulkCopy.BatchSize. – HasanG Dec 27 '10 at 08:31

score 8 · Answer 3 · answered Dec 26 '10 at 23:30

Change the CommandTimeout property of your SqlCommand object.

The Connect Timeout attribute of a connection string determines how long a SqlConnection Object runs before it stops attempting to connect to a server.

Also change the SqlBulkCopy.BulkCopyTimeout Property.

Timeout expired with SqlBulkCopy

3 Answers3

Linked