SqlBulkCopy class problem - Timeout expired!

[CleverMindworks]

So, apparently there is a known bug:
http://support.microsoft.com/?kbid=913177

A have applied the patch, and yet I'm now consistently getting the
'timeout expired' error. The symptoms match the article, yet the patch
didn't seem to fix anything.

Does anyone have any insight into this problem and if it's going to be
fixed properly?

The exact stacktrace is:
t System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)\r\n at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)\r\n at
System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject
stateObj, UInt32 error)\r\n at
System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult
asyncResult, TdsParserStateObject stateObj)\r\n at
System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32
bytesExpected)\r\n at
System.Data.SqlClient.TdsParserStateObject.ReadBuffer()\r\n at
System.Data.SqlClient.TdsParserStateObject.ReadByte()\r\n at
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)\r\n at
System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()\r\n at
System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32
columnCount)\r\n at System.Data.SqlClient.SqlBul
kCopy.WriteToServer(DataTable table, DataRowState rowState)\r\n at
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

Cheers!
-Sean

 

[CleverMindworks]

Well, as sad as it is, it looks like using the
"MultipleActiveResultSets=true" in the connection string does fix the
problem. Although it's not recommended by MS, it seems to be the only
thing that fixes the issue.

(Hey, MS, can we get a real patch?)

Hope this helps someone..

cheers!
-Sean

 

[CleverMindworks]

Doh! Looks like a mirage.. when I get over about 150K records, the
problem seems to manifest.

... the search continues.
-s

 

[David Browne]

Doh! Looks like a mirage.. when I get over about 150K records, the
problem seems to manifest.

.. the search continues.
-s

Can you post a repro?

David

 

[CleverMindworks]

Hi David,

thanks for the reply! Turns out that our production server has 19+
million rows that indexes are being updated on during insert.

So, in this case, the problem was a legitimate timeout caused by the
index design. I reduced the number of, and optimized the indexes
(removing the clustered GUID column) and insertion time is good again.

We didn't see this problem on our dev box because we didn't have the
millions of rows.

So, maybe the patch does work?

Cheers!
-Sean