DB Connections Remain Open After Application Requests Close

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written several ASP.NET applications and web services that open
ADO.NET connections. I rigorously follow the following programming construct.

SqlConnection sqlConn;
try
{
...
sqlConn.Open();
...
}
catch(Exception err)
{
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}

However, when I look at the DB Server Processes in Enterprise Manager I see
the one or more connection still open, even after I have close the brower
session or shutdown the web service client. These connections can remain
open for a half hour or more. What is going on?
 
Half an hour? Interesting. Try to restart IIS and see if that doesn't clear
them. I would expect 4-8 minutes after the connection is closed...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"(e-mail address removed)"
 
I might try this on development box where everything is on same machine.
This is not an option on the production web server.
 
This behavior is occuring in both development and production. The production
environment consists of a single web server and two database servers.
 
So, can I assume you're checking the SQL Server sp_who2, performance
counters or somesuch to determine that there are connections left open/idle?
The ADO.NET perf counters are not reliable if that's what you're using to
determine if connections are hanging. They aren't fixed (replaced) until
2.0.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"(e-mail address removed)"
 
I am actually looking at Management-Current Activity-Process Info information
in Enterprise Manager to determine which connections are open on the server
and which user is holding them.

At issue is some automated administrative activity scheduled for 3:00am does
not run correctly because these lingering connections. There are several
ASP.NET applications and Web services that are holding these connections. I
noticed the same behavior on my development machine. The connections do go
away after some time. The problem is that they persist for much too long.

It seems that ADO.NET is creating some sort of temporary connection pool
that gets flushed after some idle period for the application/service. Is
anyone aware of something like this? We have other non .NET applications
that are using the legacy ADO component that do not exhibit this behavior
 
All SQL Server providers have implemented connection pooling for some time
now (many years) so I expect that there may be other issues involved.
I wrote a whitepaper on how the connection pool works that might help. (see
http://www.betav.com/msdn_magazine.htm and
http://www.betav.com/sql_server_magazine.htm)
If the connections are not closing within 4-8 minutes of being closed by the
application, then there is something else wrong. What version of .NET are
you using? 1.1 SP1? There have been a number of patches that address
connection issues so I would make sure you're up to date. However, if the
asp/web service applications aren't properly closing the connections, then
this indeed might be your problem. It's a pretty common scenario. The
articles address those issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"(e-mail address removed)"
 
Back
Top