2

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.

4
  • Is this an ASP.NET web app on IIS?
    – Kyle
    Aug 22, 2009 at 19:09
  • I think it is Kyle. Aug 22, 2009 at 19:55
  • what version of SQL in the test environment?
    – SqlACID
    Aug 22, 2009 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. Aug 22, 2009 at 20:31

3 Answers 3

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.

1
  • 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!). Sep 11, 2009 at 10:34
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

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

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .