The client was unable to reuse a session with SPID XX, which had been reset for connection pooling.

Question

The client was unable to reuse a session with SPID XX, which had been reset for connection pooling.

gurbirrataul

SSC Veteran

Points: 251
More actions
February 29, 2012 at 9:34 am

#265364

Hi Guys,
Need your help! We are receiving the below errors more frequently now. When ever we get this error, application gets timeout errors of Server not responding (System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding).
Here is the configuration of Production server :
Window server 2008 R2 Enterprise X64 bit; RAM:32 GB; Processors: 2 ( 16 Core each);
SQL Server : SQL Server 2008 R2 Enterprise CU4; Memory 1GB-29GB (max); MDOP=1 (OLTP Server);
I am getting this below error almost every day and it causing failure in the application.
Error :
2012-02-29 09:00:52.350 Logon Login failed for user 'XXX'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. [CLIENT: IP]
2012-02-29 09:00:52.350 spid74 Error: 18056, Severity: 20, State: 46.
2012-02-29 09:00:52.350 spid74 The client was unable to reuse a session with SPID 74, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error.
I have researched on the Error a lot, did not find any particular solutions to it. I have found this link where this has been reported as bug to Microsoft, but no resolutions were posted.
http://connect.microsoft.com/SQLServer/feedback/details/540092/sql-server-2008-sp1-cu6-periodically-does-not-accept-connections
So is this bug fixed or not ? Can you guys please help me to find the solution. do I need make any changes to setting in SQL Server.
Please help me!!!!
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Wayne West SSC-Insane Points: 22586 More actions · Answer 1

I've also seen this error on one of my two production SQL 2008 instances, the other is really just beginning to get in to production so maybe that's why I haven't seen it there yet.

I read the Microsoft URL that you posted and they mentioned that it can happen when a connection is trying to change the database to or from a login's default detabase, so I did some digging and reviewed every user login on that instance and found LOTS that did not have a default database. In my defense, they were created by other people or applications. Friday I added the appropriate defaults and the error has not yet returned, but two weekend days is hardly proof that it corrected the problem.

I think the errors were being caused by programs that had their own application servers as those were the bulk of logins that were missing default databases, probably the application servers handled the errors themselves because I never heard of any actual users having issues.

I'll let you know after a couple of weeks go by.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

paresh_bhadekar SSC Enthusiast Points: 130 More actions · Answer 2

Hi Team,

Can anybody suggest.

We received below errors.

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

"New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 96%."

We have SQL Server 2008 R2 +SP2 + CU1

is there any solution for the same.

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

Paresh, have you checked the default databases for your user logins? A starting point to look at this would be something like:

select name, dbname from syslogins

Syslogins is deprecated and is replaced with system views, but it still runs on SQL 2014 so you should be fine with your 2008 environment. Odds are that a lot of your logins are pointing to master rather than your production database.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

SQL ADMIN 79 SSCommitted Points: 1993 More actions · Answer 4

Receiving below error. Has anyone come across this error?

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

Login failed for user 'XXX'. Reason: Failed to open the database 'XXX' configured in the login object while revalidating the login on the connection.
Error: 18456, Severity: 14, State: 46.

Prakash B