SQL 2012 - SQL Connections not getting Closed by Client Application and Impact Feedback Request

Question

Post reply

SQL 2012 - SQL Connections not getting Closed by Client Application and Impact Feedback Request

mattmc

Right there with Babe

Points: 787
More actions
March 31, 2014 at 11:14 pm

#287174

Hi all,
I have been investigating the number of connections active\inactive to a certain database server and I have stumbled across an application which seems to not be clearing its database connections.
For one instance of a client there was >70 sql connections which eventuated from the closing and reopening one 1 screen in the culprut app. Once the application was closed all of the connections are recycled but its evident that within the application itself it is not correctly reusing already existing open connections.
I have raised a point with the main programmer that we need to investigate more into how the application is managingot managing its ADO .NET connections to SQL and I suppose I was after any advise\feedback from any DBA's who have come across similar situations before?
I am starting with doing some reading here http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx and I was hoping to get some more information about the possible impact of excessive sql connections on the SQL Server itself. Our organization is quite lucky in that our SQl Servers are Overspecced given their workload, bearing that in mind I would like to dig a bit deeper to get some stats if I can to highlight the scope of the issue to the management\programmers.
Our SQL server peaks at 6500 processes and a good 70% of those are due to this applications mis-management of its sql connections.
Any feedback\Advise appreciated.
Kind regards,
Matt

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Check following link:

http://msdn.microsoft.com/en-us/library/vstudio/8xx3tyca(v=vs.100).aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

mattmc Right there with Babe Points: 787 More actions · Answer 2

I have already and it further highlights that the coded applications are not behaving correctly pertaining to their connection management.

The idea behind connection pooling is that it re-using its already existing connections in the pool, not purely continuing to add connections to the "pool"

when there is an unused connection already within the pool that may be used.

I could go to the lengths of creating a .NET demo app to demo this to the programmers if that's what it takes.

Any advice as always appreciated.

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 3

Not sure why you need to explain to programmers why closing database connections after they're used is important.

mattmc Right there with Babe Points: 787 More actions · Answer 4

Neither can I but here we are.

When i was auditing the server i found the issue and engaged the programmers and

the explantion i got was "its connection pooling thats how it works" which is incorrect.

I refuse to beleive that X1 application instance needs to have 75 plus connections to the database when its only activly using one.

Seems like a coding error but its unfortunate that i backup my "claim" in order to get the issue looked at.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

I have witnessed a similar issue when developing SSIS packages, where the connection retains the lock when developing packages in BIDS even though the packages are in development mode.

Which is a bit of an issue if a developer is working on the test server and forgets to close down the environment at the end of the day and you get in and find that the overnight ETL process is stalled and you need to wait 1-2 hours for it to complete.

We could stop the job but its purpose is to test new ETL changes as well as having a reasonably up to date data set for the report writers, developers and others to work with.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 6

The programmers are definitely wrong (as you already know). They need to use one connection and decide either to keep it open all the time and close on exit, or to open and close it each time they interact with the database. For the former, they could put the following in front of each database action (this is C#, but other ADO.NET will be similar);

if (myConnection.State != ConnectionState.Open)

{

myConnection.Open();

}

If they opt for the second method (preferred), they'll need to have database interactions inside try/catch blocks (they should anyway) and use the following in the "finally" part;

if (myConnection.State == ConnectionState.Open)

{

myConnection.Close();

}