Solved

Max pool size was reached.

Posted on 2011-02-23
14
1,692 Views
Last Modified: 2012-05-11
All,

I am receiving the following error message on one of my applications. "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

I have read numerous posts that suggests I alter my ASP code or change my connection string, but the application I working with is an app that we purchased and I cannot change the code of the app. Is there anything I can do from the SQL Server side or in IIS? Thanks!
0
Comment
Question by:jsimonuchc
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34964776
This is not about changing the ASP code but app pool max settings on your website application/connection pooling.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964787
query timeout can be effected by the "query wait (s)" setting of the sql sever... to set it to endless value run the script below.

sp_configure 'show advanced options', 1
reconfigure

sp_configure 'query wait (s)', -1
reconfigure

sp_configure 'show advanced options', 0
reconfigure




      
0
 
LVL 1

Author Comment

by:jsimonuchc
ID: 34964852
I can't see anywhere in the application settings to change the pool settings.

Tigin44: What other effects will this change have?
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964873
you should run the above scripts against the server


0
 
LVL 1

Author Comment

by:jsimonuchc
ID: 34964890
What other effects will running that script have on my application?
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34965020
its probably that "query wait (s)" running value is set to -1 meaning queirws will run till they complete...
you can check the detailed information from books online about sp_configure......
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34965136
Connection Pooling for the .NET Framework Data Provider for SQL Server
http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx

Using Connection Pooling with SQL Server
http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

you set the values in your config files/connection string for the Max Pool Size

or maybe you were hit by the bug below:
http://support.microsoft.com/kb/948868/en-us

or maybe you only need to close your data reader before close connection
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34967119
>>What other effects will running that script have on my application? <<
It will have no effect on your application.  A timeout is a client setting and has nothing to do with the server.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34970118
I would debate that as by letting the query to wait indefinitely could seriously impact the server and implicitly the APP (sorry to say it) on poorly written SQL code. Please see note below from Microsoft and consider twice before changing it. I would rather decrease it instead of making unlimited and if you have a APP performance issues there are many other ways to detect/deal with it.

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

"A transaction that contains the waiting query may hold locks while the query waits for memory. In rare situations, it is possible for an undetectable deadlock to occur. Decreasing the query wait time lowers the probability of such deadlocks. Eventually, a waiting query will be terminated and the transaction locks released. However, increasing the maximum wait time may increase the amount of time for the query to be terminated. Changes to this option are not recommended."

 
0
 
LVL 1

Author Comment

by:jsimonuchc
ID: 34970158
lcohan: As I mentioned, I cannot make any changes to the application code, but I will look at the hotfix issue you describe
0
 
LVL 1

Author Comment

by:jsimonuchc
ID: 34970227
It looks like Windows Server 2008 R2 has that hotfix included in it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34976368
The problem is really quite simple.  You are trying to execute a query that is exceeding the amount of time allocated to it by the client.  That is it.

For example, if this application was developed using .NET then the default command timeout is 30 seconds, so if this was not changed and a particular SQL query or Stored Procedure took longer than 30 seconds, you would ger the error.

What can you do about it?  Since it is a third party application you need to contact the vendor and explain the problem.  They are the ones that are going to have to address it.
0
 
LVL 1

Author Comment

by:jsimonuchc
ID: 34979338
That's what I was afraid of. I'll keep this thread open for another day or so and see if there are any other ideas, and the I'll close it.
0
 
LVL 1

Author Closing Comment

by:jsimonuchc
ID: 34998626
I have a ticket in with the vendor. They have acknowldeged the problem and are working on it.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now