I get this error when calling an ASP.NET service. Any idea what this is and how to prevent it?
System.InvalidOperationException: 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.
Well I do see someone put recycle threads at 1740 minutes...which is normally 20minutes. Do you think that could cause the problem?
No. That's not the problem. If you're leaking connections you must fix that. The default recycle is set at 1740 iirc for IIS 6, not 20 minutes. You do not want to recycle every 20 minutes! That's like getting a bigger bucket to scoop out the water of your boat instead of fixing the holes. It'll work for a time, but sooner or later you'll sink anyway.
Fix the code.
I can see where the SQL connections were not closed. So I added the close code as well. But these are one time hits to a service so I see no reason why the worker processes could not be recycled sooner and save on any memory of any "hung" threads.
If you have hung threads you should ensure that they do not hang. Killing the entire worker process is sort of like than swatting flies with a sledgehammer, you'll mostly miss and in most cases cause more harm in the process of trying than was originally caused by the problem. (This seems to be my day for analogies... ;-) Depending on process recycling to fix problems that should be fixed in code is, well, just simply bad. In the best case you'll have less performance than you'd get otherwise (this may not be a concern by itself, if you have capacity to spare but...) in the worst case, which is the common case, you'll get an unstable site that sometimes works, and sometimes do not. Also - if you're using sessions stored in process, the users will loose these sessions at the time of the recycle - in effect, every 20 minutes all the currently active users will loose their session. That's not so good.
If you have anything cached - you loose it. You'll have to reload the entire AppDomain. All in all - worker process recycling is pretty costly. Don't use it as a way to hide the bugs in your code!
You should engineer your site to be able to run forever. You may for example check this link:http://www.asp.net/faq/AspNetAndIIS6.aspx - note that automatic process recycling is not recommended! (There are some various views on this subject, but you'll find no-one recommending it every 20 minutes for any reason!)
Reactive process recycling, i.e. as a response to an error condition, is ok since it increases the application up-time - but should always be accompanied by an alarm, so that the root cause can be eliminated.
I just talked with the guy that wrote the code causing the problem as I still close connections and try and manage memory like an old ASP programmer. But he and I attended classes on asp.net where Microsoft reps told us there is no need to close SQL connections or do any of this stuff as asp.net as automatic garbage collection and automatically cleans itself up from all of this.
But what this shows is, we are right back to the point where a users bad code can take down the entire web server...not a great feeling.
Don't know what the Microsoft reps were taking, but unfortunately they were wrong and you are right in more ways than one.
Garbage collection does remove the risk for memory leaks, unfortunately it introduces the twofold problems of indeterminite response time (you can't know when a garbage collection may be required, thus cannot guarantee a minimum response time which makes the whole platform more or less useless for anything approaching real-time scenarios) and memory fragmentation in the large object heap in the end leading to out of memory conditions (one of the few reasons you may in fact have to recycle).
Garbage collection does not solve the problem of unmanaged resources, nor does it solve the problem of untimely shortage of some resources. The database pool connections will in fact be reclaimed at some point by garbage collection, but shortage of such a resource does not trigger a forced gc to that will ensure any loose ones to be reclaimed - causing the effects you noted.
So - If an object implements IDisposable, you're stuck with managing it and ensuring it's timely disposal just like in the old days. But there is the nice 'using' statement to help you with that chore... ;-)
