I have a situation where an application is over time opening a cumulative 6000 + connections to a 32 bit SQL2005 SP2 backend from a couple of app servers eventually causing internal memory pressure (dll unload from mem2leave area messages in log whilst simultaneously crashing app). I assume that the application is at fault (not closing connections correctly). I've thrown this back at the app guy but he's suggesting that the problem may lie with SQL server as it's not occurring on a similar UAT environment. Any suggestions for what I can do on the SQL side? I've considered increasing the mem2leave area but worried this will just delay/mask the real problem.

what version of SQL in the test environment?
–
SqlACIDAug 22 '09 at 20:21

All the environments are the same (SQL2005 SP2). The UAT box houses additional instances for other app databases. The Production instance is a two node active / passive cluster.
–
SuperCoolMossAug 22 '09 at 20:31

3 Answers
3

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.

Yes, it was an app is issue. Thanks for the answer - getting the application guy to run Netstat on the app server pretty much proved it conclusively. They've now fixed the problem and all's well in world (for now!).
–
SuperCoolMossSep 11 '09 at 10:34

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: