|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DB Connections Remain Open After Application Requests CloseADO.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... -- Show quote____________________________________ 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. __________________________________ "Eagle***@HighFlyingBirds.com" <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message news:500A6A4E-3BCB-4663-AF61-AA6890780750@microsoft.com... >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? I might try this on development box where everything is on same machine.
This is not an option on the production web server. Show quote "William (Bill) Vaughn" wrote: > 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. > __________________________________ > > "Eagle***@HighFlyingBirds.com" > <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message > news:500A6A4E-3BCB-4663-AF61-AA6890780750@microsoft.com... > >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? > > > This behavior is occuring in both development and production. The production
environment consists of a single web server and two database servers. Show quote "William (Bill) Vaughn" wrote: > 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. > __________________________________ > > "Eagle***@HighFlyingBirds.com" > <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message > news:500A6A4E-3BCB-4663-AF61-AA6890780750@microsoft.com... > >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? > > > 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. -- Show quote____________________________________ 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. __________________________________ "Eagle***@HighFlyingBirds.com" <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message news:C6EB6F57-C36A-446B-A113-6B94E2E793E6@microsoft.com... > This behavior is occuring in both development and production. The > production > environment consists of a single web server and two database servers. > > "William (Bill) Vaughn" wrote: > >> 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. >> __________________________________ >> >> "Eagle***@HighFlyingBirds.com" >> <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message >> news:500A6A4E-3BCB-4663-AF61-AA6890780750@microsoft.com... >> >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? >> >> >> 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 Show quote "William (Bill) Vaughn" wrote: > 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. > __________________________________ > > "Eagle***@HighFlyingBirds.com" > <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message > news:C6EB6F57-C36A-446B-A113-6B94E2E793E6@microsoft.com... > > This behavior is occuring in both development and production. The > > production > > environment consists of a single web server and two database servers. > > > > "William (Bill) Vaughn" wrote: > > > >> 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. > >> __________________________________ > >> > >> "Eagle***@HighFlyingBirds.com" > >> <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message > >> news:500A6A4E-3BCB-4663-AF61-AA6890780750@microsoft.com... > >> >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? > >> > >> > >> > > > 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. -- Show quote____________________________________ 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. __________________________________ "Eagle***@HighFlyingBirds.com" <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message news:D9BC8598-CA28-4079-9D48-7EF00064F6C7@microsoft.com... >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 > > "William (Bill) Vaughn" wrote: > >> 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. >> __________________________________ >> >> "Eagle***@HighFlyingBirds.com" >> <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message >> news:C6EB6F57-C36A-446B-A113-6B94E2E793E6@microsoft.com... >> > This behavior is occuring in both development and production. The >> > production >> > environment consists of a single web server and two database servers. >> > >> > "William (Bill) Vaughn" wrote: >> > >> >> 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. >> >> __________________________________ >> >> >> >> "Eagle***@HighFlyingBirds.com" >> >> <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in >> >> message >> >> news:500A6A4E-3BCB-4663-AF61-AA6890780750@microsoft.com... >> >> >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? >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics