[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Bulkload error in SQL Server 2008

Posted on 2010-11-22
5
Medium Priority
?
3,102 Views
Last Modified: 2012-05-10
I'm getting the following SQL Server error (Event ID 18056) when running bulkload in SQL Server 2008.  

The client was unable to reuse a session with SPID 500, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

There are no errors logged prior to this.  Following this error, I get several errors (Event ID 4014) as follows:

A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 10054, output error: 0).

My connection string is set as follows:

connectionString="provider=SQLOLEDB;data source=db_server; database=MY_DB; User ID=user;Password=xxxxxxx; pooling=false" />

My application is set up with several threads trying to run bulkload inserts of 100000 records at a time into the same database table.  This works most of the time, but after 2-3 hours, I start getting the messages listed above.  

Here's the code I use to actually execute the bulkload:

                objBL.ConnectionString = ConfigurationManager.ConnectionStrings["DynaSimBulkLoad"].ToString();
                objBL.KeepIdentity = false;
                objBL.CheckConstraints = false;
                objBL.IgnoreDuplicateKeys = false;
                objBL.KeepNulls = true;
                objBL.BulkLoad = true;
                objBL.Execute( ConfigurationManager.AppSettings["bulkloadschema"], loadFileName );

What does the session reuse error indicate here?  Any thoughts on what might be causing the error?
0
Comment
Question by:stansys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Accepted Solution

by:
stansys earned 0 total points
ID: 34192975
I found some information on this here:  

http://sqlserverpedia.com/blog/sql-server-bloggers/windows-2008-r2-needs-hotfix-for-sql-server-2008/ 

Testing out the hotfix now.
0
 

Author Comment

by:stansys
ID: 34197502
This fix did not completely resolve the issue.  It appears SQL Server is getting backed up in handling the multiple bulkload requests.  Some of the larger requests never finish.
0
 

Author Comment

by:stansys
ID: 34201361
Set the recovery model to bulk-logged and now SQL Server appears to be keeping up better with the multiple bulkload requests.  After 3 hours, the app is still stable.  
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 34276901
good news I was going to suggest set the recovery to bulk
0
 

Author Closing Comment

by:stansys
ID: 34317124
Turns out the Microsoft fix resolved much of the problem.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question