[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2744
  • Last Modified:

Max pool size was reached.

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
jsimonuchc
Asked:
jsimonuchc
  • 6
  • 3
  • 3
  • +1
1 Solution
 
lcohanDatabase AnalystCommented:
This is not about changing the ASP code but app pool max settings on your website application/connection pooling.
0
 
tigin44Commented:
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
 
jsimonuchcAuthor Commented:
I can't see anywhere in the application settings to change the pool settings.

Tigin44: What other effects will this change have?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tigin44Commented:
you should run the above scripts against the server


0
 
jsimonuchcAuthor Commented:
What other effects will running that script have on my application?
0
 
tigin44Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
Anthony PerkinsCommented:
>>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
 
lcohanDatabase AnalystCommented:
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
 
jsimonuchcAuthor Commented:
lcohan: As I mentioned, I cannot make any changes to the application code, but I will look at the hotfix issue you describe
0
 
jsimonuchcAuthor Commented:
It looks like Windows Server 2008 R2 has that hotfix included in it.
0
 
Anthony PerkinsCommented:
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
 
jsimonuchcAuthor Commented:
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
 
jsimonuchcAuthor Commented:
I have a ticket in with the vendor. They have acknowldeged the problem and are working on it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now