Solved

Max pool size was reached.

Posted on 2011-02-23
14
1,700 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Lync server 2013 or Skype for business Backup Service Error ID 4049 – After File Share Migration
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

20 Experts available now in Live!

Get 1:1 Help Now