The client was unable to reuse a session with SPID 57, which had been reset for connection pooling.
Question
Tuesday, December 11, 2012 6:19 AM
Hi ,
We are getting error reoccurring in SQL Error Logs,(Error: 18056, Severity: 20, State: 29) on production server ,version is SQL Server2008R2 SP1. Can anyone please explain this issue and resolution as well.
Thanks
All replies (20)
Tuesday, December 11, 2012 3:14 PM ✅Answered | 4 votes
Hello this type of an issue occurs when ever your SQL resources are tightly busy with the existing connections & its operations that performing against on the database level or overall your resource crunch so you need to Identify who is the culprit over here(it might be your SQL queries or any other Bottleneck) either you are using Server side trace to run during that point of time,again it is not for the same reason can it fail it might be other reason as well...!
Also see
Hotfix for SQL Server 2008/2008 R2 Periodically Does Not Accept Connections Bug
Error: 18056, Severity: 20, State: 29.
http://www.sqlservercentral.com/blogs/glennberry/2011/11/26/hotfix-for-sql-server-2008_2F00_2008-r2-periodically-does-not-accept-connections-bug/
Also Cumulative update information requires to apply
http://support.microsoft.com/kb/2543687
see sample one
How It Works: Error 18056 - The client was unable to reuse a session with SPID ##, which had been reset for connection pooling
http://blogs.msdn.com/b/psssql/archive/2010/08/03/how-it-works-error-18056-the-client-was-unable-to-reuse-a-session-with-spid-which-had-been-reset-for-connection-pooling.aspx
Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.
Friday, December 21, 2012 10:42 PM ✅Answered
The service pack only relieves you from the noise of state 29 for message 18056.
These messages indicates that you have a real problem. Not a problem in SQL Server, though, but somewhere else in your network. Searching on error 17806 I found a couple of interesting entries:
http://dbaduck.com/2007/08/31/error-in-sql-error-17806-error-18452-sspi-problem/
http://www.allenkinsel.com/archive/2010/06/sql-server-and-sspi-handshake-failed-error-hell/
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/642272d5-2a3b-43de-b1a8-cff8be317292
I did not go through them in detail, but I hope that they help you in your troubleshooting.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, December 11, 2012 2:17 PM
This is a very generic error. To narrow down on the reason, please check what other processes are running and the overall resource utilization of the server/instance when this error occurs. This thread has good discussion around this issue.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @PradeepAdiga
Friday, December 14, 2012 3:43 AM
Thanks For the response .
Actually We have 2 instance in a SQL server default one is using 1433 port and the another one is dynamic port, user able to connect with the default instance databases but not able to access the named QA instance and when I checked the error logs ,both instances have same error in logs as"Error: 18056, Severity: 20, State: 29". The named instance is not so busy after that also user facing issue to connect with the databases. And the issue is started came recently and we r using this server more than a year. Suddenly the error started to come.
If its bug of Microsoft then problem of connectivity should be there since the server has configured but we are facing this issue from last month only.
Please help me to resolve this issue.
Thanks/
Friday, December 14, 2012 10:21 PM
I would recommend that you install the latest service pack. That will stopp
the message from appearing in the errorlog. The fix is simply that the error
is not raised for state 29, as it has proven to be more noise than helpful.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, December 15, 2012 2:50 PM
I would recommend that you install the latest service pack. That will stopp
the message from appearing in the errorlog. The fix is simply that the error
is not raised for state 29, as it has proven to be more noise than helpful.Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Hi Erland,
I agree that applying service pack/hot fix will fix the issues(as I said in earlier my mail in the same mail chain) may or may not, but few cases I have seen in my environment that the culprit is that lot of ad-hoc logins and queries called by business without knowing to us but again Iam not saying that this is the problem here it might be other issues as well hence approached different solutions.. some times the application/business not ready to move their sevice pack to latest because their applications tightly depends on them, ofcourse it is not the good option to keep as it is but its up to us how we approach to the business.. and their understanding..
Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.
Saturday, December 15, 2012 4:57 PM
So the gist of what I said is that in recent service packs, SQL Server does not produce this message with state 29. That is, the fix is exactly that: do not produce the error message.
From this my conclusion is that until you have installed the most recent service pack, you can consider this message to be white noise. Note that this only applies to state 29.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, December 21, 2012 6:47 AM
Hi All,
I have upgraded the server with service pack2(SQL server 2008 R2 SP2). Still I can find error in error logs like
"Error: 17806, Severity: 20, State: 14."
SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security;
And
"Error: 18452, Severity: 14, State: 1."
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Can anyone help me to explain these error and proper resolution...still applications are facing issue during connectivity with Database.
Thanks
Monday, December 31, 2012 4:51 AM
Hi All,
state 29 for message 18056 still coming on errorlogs after upgraded the SQL server 2008 R2 with sp2.
Please help me to resolve this.I tried all the provided solution but issue still error is occurring.
Thanks
Monday, December 31, 2012 4:52 AM
Hi All,
state 29 for message 18056 still coming on errorlogs after upgraded the SQL server 2008 R2 with sp2.
Please help me to resolve this.I tried all the provided solution but issue still error is occurring.
Thanks
Monday, December 31, 2012 4:52 AM
Hi All,
state 29 for message 18056 still coming on errorlogs after upgraded the SQL server 2008 R2 with sp2.
Please help me to resolve this.I tried all the provided solution but issue still error is occurring.
Thanks
Monday, December 31, 2012 10:16 AM
From what I've been told, they no longer produce that message with that particular state. Furthermore, the message in itself is not a problem. If you have no other issues with your system, you can ignore it. On the other hand, you had a post a couple of days ago which indicated that do have problems and in response to that post, I posted couple of links that could help you troubleshoot those problems.
As for error 18056, state 29, here are the top 3 hits I got on Google when searching for 18056 and 29. All links seems to be useful.
By the way, I would double-check that you have actually installed the service pack. That's simple, just look at the top of the log where you see this error. I note that you said in your initial post that you had two instances. Maybe you only upgraded one of them?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Wednesday, January 2, 2013 2:38 AM
Hi,
I have upgraded both the instances with SP2...Still the error is occurring in both instances.
When I am restarting the SQL Browser service the issue is resolving temporary...After 5-6 days again application facing issue to connect with database and when I am checking the error logs its showing the same errors(Error: 18056, Severity: 20, State: 29",Error: 17806, Severity: 20, State: 14 and Error: 18452, Severity: 14, State: 1).
Still its confusing me that browser services restart how is impacting and what is the root cause and permanenent solution.
Thanks
Wednesday, January 2, 2013 10:55 PM
So have you pursued any of the links I have posted?
If you are not able to make anything out of these links, I would recommend that you open a case with Microsoft, as it does not seem that we come any further in this forum. There is obviously a problem in your network and it may require some understanding of Windows networking and Active Directory to track this down, and that is certainly no knowledge I have myself.
A support case will cost you an arm and a leg (unless in the unlikely case that there actually is a bug in Windows or SQL Server), but it is likely that you will arrive at a solution faster in this forum, because to be frank, I feel that we are getting nowhere. I've been trying to help as much as I can by finding links that seems useful, but if you are interesting in reading these, or you do not find them applicable, there is not more than I can do.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Thursday, September 14, 2017 5:33 PM
I am having the same issue.
Error:
Message
The client was unable to reuse a session with SPID 483, 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.
Message
Error: 18056, Severity: 20, State: 46.
What is the resolution for this error?
Thursday, September 14, 2017 6:26 PM
The same symptoms may not necessarily require the same resolution. Rather than add to an answered thread that is years old, I suggest you ask a new question. Specify the SQL Server version and patch level as well as any related messages from the SQL Server error log.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Monday, November 26, 2018 9:40 PM
What it resolution for error 18056 severity 20 state 46?
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.
Monday, November 26, 2018 10:49 PM
So did you see the answer Dan posted a year ago? Or why are you not following the instructions for how to ask questions?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, November 27, 2018 2:13 PM
Called Microsoft , opened a premier case. Issue still remains persistent. SQL Server version is SQL 2012. The only recommendation provided by Microsoft was to upgrade the instance to the latest service pack.
Tuesday, November 27, 2018 10:35 PM
Yes, you have to be on a supported version to get any help.
And here in the forums, you need to start a new thread describing your problem from start to end. Piggybacking on old threads as you have been doing in several forums, is not going to help you.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se