Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The client was unable to reuse a session with SPID 799, which had been reset for connection pooling. Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 4:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:50 AM
Points: 19, Visits: 151
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
Posted Thursday, October 25, 2012 7:19 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:14 PM
Points: 91, Visits: 164,592
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
Posted Thursday, October 25, 2012 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:50 AM
Points: 19, Visits: 151
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
Posted Thursday, October 25, 2012 11:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 946, Visits: 1,774
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
Posted Sunday, April 27, 2014 1:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:22 PM
Points: 198, Visits: 83
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.
Post #1565396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse