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

Connection pooling troubleshooting in SQL 2008, client unable to reuse a session with SPID... Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 7:24 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: Yesterday @ 3:30 PM
Points: 810, Visits: 2,120
Hi folks, we have recently moved four SQL 2000 servers to three SQL 2008 servers. These are servers that connect to our web application which is load balanced on the back end to the three SQL Servers. Each server is Standard 64-bit on a Windows 2008 server standard. The new servers are 12GB ram, 8 core vs. 4GB ram, 4 core in the old servers.

We have been getting intermittant Sev. 20 errors of the following format:

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

and the error in the log that occurs in conjunction with this error is: Error: 18056, Severity: 20, State: 29.

This seems to be a new thing in SQL 2008, as most references I've found online refer to SQL 2005, but also that it has been patched, which I assume is still fixed in SQL 2008.

I'm still waiting to get information from the online folks to see if they found any errors in their IIS logs to correlate with my errors, but has anyone received this type of error and made some headway in troubleshooting this?

Our web site still seems to be okay, considering we're retail and in a holiday season so orders are still flowing, but I'd rather not filter these warnings away from the pager if this is a symptom of something serious down the road, rather than an annoying feature I can safely ignore.

Thanks.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #832207
Posted Thursday, December 10, 2009 9:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 3,433, Visits: 14,423
The error you are getting says two things your databases are in compatibility 80 and your application is also old because this problem is fixed for SQL Server 2008 and .NET 3.5. So change to compatibility 100 and rewrite your connection code if your Asp.net application is Asp.net 2.0 because Asp.net 3.5 compiles as 2.0.



Kind regards,
Gift Peddie
Post #832373
Posted Thursday, December 10, 2009 10:36 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: Yesterday @ 3:30 PM
Points: 810, Visits: 2,120
Gift Peddie (12/10/2009)
The error you are getting says two things your databases are in compatibility 80 and your application is also old because this problem is fixed for SQL Server 2008 and .NET 3.5. So change to compatibility 100 and rewrite your connection code if your Asp.net application is Asp.net 2.0 because Asp.net 3.5 compiles as 2.0.



Compatibility was changed from 80 to 100. As for the asp.net 3.5 issue, I'll forward the info to the developers, but was wondering if you had some URL's where you got this information from so I can help them troubleshoot it.

Thanks.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #832412
Posted Thursday, December 10, 2009 12:26 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 3,433, Visits: 14,423
GabyYYZ (12/10/2009)
Gift Peddie (12/10/2009)
The error you are getting says two things your databases are in compatibility 80 and your application is also old because this problem is fixed for SQL Server 2008 and .NET 3.5. So change to compatibility 100 and rewrite your connection code if your Asp.net application is Asp.net 2.0 because Asp.net 3.5 compiles as 2.0.



Compatibility was changed from 80 to 100. As for the asp.net 3.5 issue, I'll forward the info to the developers, but was wondering if you had some URL's where you got this information from so I can help them troubleshoot it.

Thanks.



Here is the link go to the section that says extension added to SQL Server 2008 and it also says you don't have to make changes to the code but I think the code needs to be checked to make changes as needed to the connection pooling code.

I almost forgot if the shopping cart is commercial version look into the code used to consume the cart transaction object.


http://msdn.microsoft.com/en-us/library/ms172070.aspx


Kind regards,
Gift Peddie
Post #832501
Posted Thursday, December 10, 2009 12:38 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 810, Visits: 2,120
Gift Peddie (12/10/2009)
GabyYYZ (12/10/2009)
Gift Peddie (12/10/2009)
The error you are getting says two things your databases are in compatibility 80 and your application is also old because this problem is fixed for SQL Server 2008 and .NET 3.5. So change to compatibility 100 and rewrite your connection code if your Asp.net application is Asp.net 2.0 because Asp.net 3.5 compiles as 2.0.



Compatibility was changed from 80 to 100. As for the asp.net 3.5 issue, I'll forward the info to the developers, but was wondering if you had some URL's where you got this information from so I can help them troubleshoot it.

Thanks.



Here is the link go to the section that says extension added to SQL Server 2008 and it also says you don't have to make changes to the code but I think the code needs to be checked to make changes as needed to the connection pooling code.

I almost forgot if the shopping cart is commercial version look into the code used to consume the cart transaction object.


http://msdn.microsoft.com/en-us/library/ms172070.aspx


Thanks, I have been scratching my head trying to find a link that talks about this explicitly and I've forwarded it to our developers. As the web servers don't seem to be impacted, I'm downgrading our alerts so we still receive them, but I'm just not paged at 3am. It's also possible, due to holiday traffic, that greater spikes mean the potential for more issues on the network.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #832516
Posted Thursday, December 10, 2009 12:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 3,433, Visits: 14,423
GabyYYZ (12/10/2009)
Gift Peddie (12/10/2009)
GabyYYZ (12/10/2009)
Gift Peddie (12/10/2009)
The error you are getting says two things your databases are in compatibility 80 and your application is also old because this problem is fixed for SQL Server 2008 and .NET 3.5. So change to compatibility 100 and rewrite your connection code if your Asp.net application is Asp.net 2.0 because Asp.net 3.5 compiles as 2.0.



Compatibility was changed from 80 to 100. As for the asp.net 3.5 issue, I'll forward the info to the developers, but was wondering if you had some URL's where you got this information from so I can help them troubleshoot it.

Thanks.



Here is the link go to the section that says extension added to SQL Server 2008 and it also says you don't have to make changes to the code but I think the code needs to be checked to make changes as needed to the connection pooling code.

I almost forgot if the shopping cart is commercial version look into the code used to consume the cart transaction object.


http://msdn.microsoft.com/en-us/library/ms172070.aspx


Thanks, I have been scratching my head trying to find a link that talks about this explicitly and I've forwarded it to our developers. As the web servers don't seem to be impacted, I'm downgrading our alerts so we still receive them, but I'm just not paged at 3am. It's also possible, due to holiday traffic, that greater spikes mean the potential for more issues on the network.


Microsoft changed the title and docs so anybody can use it, the transaction can be nested at the point of connection. I think minor changes can help you handle the holiday traffic.


Kind regards,
Gift Peddie
Post #832529
Posted Monday, December 14, 2009 8:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,518, Visits: 27,899
We've been experiencing the same thing on one database on our server. I've been running in circles trying to find anything wrong on the database side of things. Finally seeing someone else with the problem and a possible solution is very useful. Thanks for posting.

For what it's worth, we have multiple databases running on the server for multiple applications and only one of them has experienced the issue. It's database is in 100 compatibility mode, so I'm going to get the developers to check their code.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #833851
Posted Thursday, March 3, 2011 8:11 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
We've been consistently seeing Error 18056, Severity 20, state 29 about the "client" was unable to reuse the session being reset for connection pooling and it says to look for a previous process which may have caused it. There is usually NOTHING happening previous to this error. Sometimes there may be an informational message prior to the error, but it looks to be coincidental and not related. We have 1 process which runs 24x7 and some which run almost that much.

Is there a way to find out who the "client" is?








Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
Post #1072587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse