Too many connections to SQL 2005

Solution 1:

A .NET app should clean it self up when the garbage collector runs on the app server. This should be automatically running every few minutes.

Can you query the SQL Server and see that these connections are still open on the SQL Server?

If you run netstat on the app server can you see all the socket connections open? (Each spid being used on the SQL Server will have a socket connection on the app server.)

If you do see all the ports in use on the app server then the app server is definitely not closing the connections as SQL doesn't close connections unless requested to. The .NET code may be expecting this to happen automatically, and it isn't. You probably don't have the issue in your test environment because the usage is much lower, and you probably to releases to the test environment more often which causes all the ports to be closed when IIS is restarted.

This definitely sounds like an app code issue to me.

Solution 2:

It sounds like the app isn't using connection pooling. This can cause issues like what you've described. You can adjust some of the TCP/IP settings for the OS. That's described in the following KB article:

Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled

Solution 3:

You are closing your connections when you finish using them, right?

Also look at your app pool settings. When it recycles, the connections will drop.