Solved

Max pool size was reached.

Posted on 2011-02-23
14
1,716 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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