Who takes care of my database connection when I don’t close it in my code?

A lot of customer has asked me a common question : How should I expect my connections to behave if I don’t close it properly in the code. Though it is highly recommended to close the connection in your code itself and not depend on any other ways to close it, I think there should be a clear idea about how we can expect our connection to behave in case it’s left open. So I did some research in out labs to find out the exact behaviour of connection on various scenarios.

Note: you can check one of my previous blog https://blogs.msdn.com/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx  to know more about connection leak and ways to troubleshoot it.

OK now coming back to the topic I want to cover today - I conducted four tests. One for native ASP, ASP .NET , .NET Windows application and a Native windows application developed in VB6. I used Windows 2003 and IIS6 and .NET 2.0 in these tests. Though the OS and the .NET version should not matter much but we can expect a slightly different behaviour with IIS5, if you application is a web application.

In the ASP application, if we do not close the connection in our code and we left the application ideal, I found that the Connections are automatically getting closed after some xx minutes. Where xx is the time set in the properties of the worker process (under which the asp application is running) when the worker process will shut down after being idle for some time.

When I did the same test in ASP .NET application (that is not closing the connection in our code and leaving the application idle), I found that there are two ways through which the connection can be closed.

1) Connection are cleaned by the time set when the worker process will shut down after being idle for some time.

2) Connection are cleaned when Garbage Collector is called and connection is out of scope.

Of the two methods above anyone that happens earlier will clean the connection.

Now let’s say that the application is not idle and still working. In that case one only way the connection could be closed is through point 2 above. But it’s hard to tell how much time would it take before the connection gets collected. Because garbage collector will implement its own logic depending on the memory pressure and some other factors to make its round time.

Now let’s talk about windows application. In case of a .NET windows application, If we do not close the connection explicitly in our code, the connection will exists until the application is closed or the garbage collector collects the connection.

But in case of a native windows application (the one that I developed in VB6 for example), the connection will persists as long as you don’t close the connection. In my test I left the application open for more than a day and I could still see the connections on the SQL.

Author : Snehadeep(MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Malcolm (MSFT) , SQL Escalation Services, Microsoft