Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2012 - SQL Connections not getting Closed by Client Application and Impact Feedback Request Expand / Collapse
Author
Message
Posted Monday, March 31, 2014 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:56 AM
Points: 25, Visits: 268
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






Post #1556807
Posted Tuesday, April 1, 2014 1:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1556825
Posted Tuesday, April 15, 2014 11:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:56 AM
Points: 25, Visits: 268
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.
Post #1562119
Posted Wednesday, April 16, 2014 12:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 23, 2014 4:20 AM
Points: 215, Visits: 877
Not sure why you need to explain to programmers why closing database connections after they're used is important.
Post #1562131
Posted Wednesday, April 16, 2014 12:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:56 AM
Points: 25, Visits: 268
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.

Post #1562135
Posted Wednesday, April 16, 2014 12:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:31 AM
Points: 922, Visits: 2,524
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
Post #1562144
Posted Thursday, April 17, 2014 4:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:31 AM
Points: 81, Visits: 271
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();
}

Post #1562586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse