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

Question

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

saurabh.x.sinha

Ten Centuries

Points: 1125
More actions
October 25, 2012 at 4:35 am

#264635

Hi
SQL server version : Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64) Enterprise Edition
Windows version : Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
My errorlog is flooded with below message and (The failure ID is 29) is in majority. A part of this I was not able to connect sql server. Finally i have restarted sql server and suddenly all these messages disappear
The client was unable to reuse a session with SPID 799, which had been reset for connection pooling.
The failure ID is 23. This error may have been caused by an earlier operation failing.
Check the error logs for failed operations immediately before this error message.
The client was unable to reuse a session with SPID 428, 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.
The client was unable to reuse a session with SPID 324, 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.
Please suggest what could be the reason for same and if there is a possibility we can fix this without restarting sql server

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thinknook SSCrazy Points: 2531 More actions · Answer 1

Please suggest what could be the reason for same and if there is a possibility we can fix this without restarting sql server

Hi,

I have experienced this in the past for one of our servers, and have posted my experience on connection resets for pooling here[/url] which includes a few places to start diagnosing the issue and a few suggested solution.

If nothing this might be a good place to start giving you ideas on where to investigate.

Cheers

/>L

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].

I believe in Codd
... and Thinknook is my Chamber of Understanding

saurabh.x.sinha Ten Centuries Points: 1125 More actions · Answer 2

Hi

Thanks for link, I tried ussing ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' as well but not able to find anything in these DMv's before sql restarted.

Seems no other go except raising MS case

CapnHector SSCoach Points: 16143 More actions · Answer 3

in your error log is the event id 18056? If so and its not causing any issues you can preaty much ignore it (im digging through my bookmarks now to find the link to where Microsoft says its not that much of an issue). you can install the latest CU for your version to quiet the event (It stops the ATTENTION but not the underlying cause)

Best thing to do is look for any bottle necks. Pegged cpu high IO contention slow network. the error is caused when the connection pool relogin throws an exception from what i have been able to gather this can happen when you have a bottle neck or have MAXDOP set wrong (in our case this did nothing YMMV). If you have a bottle neck once you fix the issue it should quit happening.

EDIT:

Here is the blog post i was talking about http://blogs.msdn.com/b/psssql/archive/2010/05/05/error-18056-can-be-unwanted-noise-in-certain-scenarios.aspx

and here is what seems to be the most helpful microsoft connect items: http://connect.microsoft.com/SQLServer/feedback/details/540092/sql-server-2008-sp1-cu6-periodically-does-not-accept-connections

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

SQLEnthusiastic Mr or Mrs. 500 Points: 593 More actions · Answer 4

No, SP2 followed by any CU does not seem to help resolve this issue. With this version "Microsoft SQL Server 2008 R2 (SP2) - 10.50.4286.0 (X64) May 29 2013 15:04:02 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) ", this error still happens. Hence 24/7 applications do suffer.

The client was unable to reuse a session with SPID 215, 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.

Anyone has found any workaround?

Thanks.

SQLEnthusiastic Mr or Mrs. 500 Points: 593 More actions · Answer 5

A possible solution is with SP3.

http://www.quicktechnotes.com/2014/11/15/session-reset-for-connection-pooling-and-sp3/

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

Receiving this error on SQL 2012 Always on. Any possible resolution?

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