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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » The client was unable to reuse a session with...

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

Rate Topic

Display Mode

Topic Options

Author

Message

saurabh.x.sinha

Posted Thursday, October 25, 2012 4:35 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:40 AM
Points: 19, Visits: 90

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

Post #1376896

ib.naji

Posted Thursday, October 25, 2012 7:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:02 PM
Points: 91, Visits: 109,006

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

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

Post #1376974

saurabh.x.sinha

Posted Thursday, October 25, 2012 9:07 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:40 AM
Points: 19, Visits: 90

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

Post #1377060

CapnHector

Posted Thursday, October 25, 2012 11:50 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 06, 2013 3:00 PM
Points: 939, Visits: 1,713

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

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

Jeremy Oursler

Post #1377179

« Prev Topic | Next Topic »

Permissions