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

    Question

  • 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

    Tuesday, December 11, 2012 6:19 AM

Answers

  • 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.


    Tuesday, December 11, 2012 3:14 PM
  • 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
    Friday, December 21, 2012 10:42 PM

All replies

  • 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

    Tuesday, December 11, 2012 2:17 PM
  • 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.


    Tuesday, December 11, 2012 3:14 PM
  • 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 3:43 AM
  • 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
    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

    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 2:50 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
    Saturday, December 15, 2012 4:57 PM
  • 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

    Friday, December 21, 2012 6:47 AM
  • 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
    Friday, December 21, 2012 10:42 PM
  • 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: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
  • 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.

    http://blogs.msdn.com/b/psssql/archive/2010/05/05/error-18056-can-be-unwanted-noise-in-certain-scenarios.aspx

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/07/06/error-18056-state-29-the-client-was-unable-to-reuse-a-session-the-failure-id-is-29-after-upgrading-sql-2000-to-sql-2008-sp2.aspx

    http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f11537a5-8dc9-40a8-b2e4-17874685b074

    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
    Monday, December 31, 2012 10:16 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 02, 2013 2:38 AM
  • 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
    Wednesday, January 02, 2013 10:55 PM