IIS Hangs on SQL Connections when running ASP.net applications

We have a database server running SQL 2000 and two web servers hosting ASP.net applications. All three servers are running Windows Server 2003 SP2.

Our issue is repeatable after about 2 weeks, IIS on one web server is no longer able to establish SQL connections. Static content loads fine. Other non-IIS applications are still able to contact the SQL database server. ODBC functionality also still works.

While running SQL profiler a connection is never established from IIS when it is in this state.

The only way to fix this situation is to restart the web server.

There are no firewalls installed on any machines.

From serverfault PaulWaldman

• If you load perfmon, and look at .NET CLR data - there are several performance counters that you can load to see the # of pools, # of pooled connections, failed connections, failed commands.

  Consider that the connection pool defaults to 100 connections in the pool per process (per appdomain) by default. Is it possible that you're looking at pool exhaustion?

  PaulWaldman : Thanks for pointing me in this direction. Before seeing your response, I stopped and started the app pool in question and the issue was resolved. The next time this issue occurs I will look at the counters. The apps hosted on this server are very low usage. The server generally only has 2 simultaneous users. Do you think the application pool could be exhausted with such low usage?

  JohnW : Its possible - 2 weeks for this to occur with only 2 users may be indicative of something wrong with the app. I'd set up perfmon to log these counters every hour or so, and you should be able to form a trend within a few days of use.

  PaulWaldman : Thanks John. After restarting the app pool the number of pool connections within the .Net SQL client was 9. Two days later, and it is up to 54. This does not seem like normal behavior to me.

  PaulWaldman : I've inherited these web apps about 2 years ago. No code changes have been made within the past year. This issue started to take place about 2 month ago. Because both of these apps are quite large, one is completely dynamic based on SQL queries, my guess is there is a function of one of the apps that they have only started to use when this issue started occurring.

  JohnW : One thing to note is that by default, IIS 6 recycles app pools every 29 hours (1740 minutes). So, unless that has been disabled/overriden for the app pool, you may be having this issue sooner than you may think. I'm not sure if this is something you could recycle every 4 hours due to the impact on user sessions, but that may buy you some time.
  From JohnW