Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Is this an ASP.NET web app on IIS? –  Kyle Hodgson Aug 22 '09 at 19:09
    
I think it is Kyle. –  SuperCoolMoss Aug 22 '09 at 19:55
    
what version of SQL in the test environment? –  SqlACID Aug 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. –  SuperCoolMoss Aug 22 '09 at 20:31
add comment

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
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!). –  SuperCoolMoss Sep 11 '09 at 10:34
add comment

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

share|improve this answer
    
Thanks for your answer - it was an application issue. –  SuperCoolMoss Sep 11 '09 at 10:35
add comment

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.

share|improve this answer
    
Thanks for your answer it was an application issue. –  SuperCoolMoss Sep 11 '09 at 10:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.