Home / server / Questions / 57434
Accepted
SuperCoolMoss
Asked: 2009-08-23 04:52:24 +0800 CST

Too many connections to SQL 2005

  • 772

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.

sql-server

3 Answers

  1. 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

    • 2

  2. 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.

    • 2
  3. Best Answer

    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.

    • 1