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

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

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

free_mascot One Orange Chip Points: 27164 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: 4606 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();

}