|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to dertiminate how many connecitonsI am use VS2005 Profession and SqlExpress 2005 to develop WebApplication.
I use many SqlDataReader in my application. I think there are some connecitons not be closed after reading data. How can I moniter the status of conneciton of a Database? 1) Use the SQL Profiler to monitor connections
2) With the 1.1 framework (try to) use the .NET data performance counters. These can show how many connections are pooled but they don't work very well. 3) With the 2.0 framework there are new performance counters (that work) 4) With any version of SS you can monitor the number of user connections using its own performance counters. I'll be discussing these (and showing the code) in my session at VSLive in Sydney. -- 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. __________________________________ "ad" <fly***@wfes.tcc.edu.tw> wrote in message news:OFfUJlh7FHA.2456@TK2MSFTNGP10.phx.gbl... >I am use VS2005 Profession and SqlExpress 2005 to develop WebApplication. > I use many SqlDataReader in my application. > I think there are some connecitons not be closed after reading data. > > How can I moniter the status of conneciton of a Database? >
http://www.eggheadcafe.com/forums/ForumPost.asp?ID=46053&INTID=9
Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:uA8LHbj7FHA.1864@TK2MSFTNGP12.phx.gbl... > 1) Use the SQL Profiler to monitor connections > 2) With the 1.1 framework (try to) use the .NET data performance counters. > These can show how many connections are pooled but they don't work very > well. > 3) With the 2.0 framework there are new performance counters (that work) > 4) With any version of SS you can monitor the number of user connections > using its own performance counters. > > I'll be discussing these (and showing the code) in my session at VSLive in > Sydney. > > -- > ____________________________________ > 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. > __________________________________ > > "ad" <fly***@wfes.tcc.edu.tw> wrote in message > news:OFfUJlh7FHA.2456@TK2MSFTNGP10.phx.gbl... >>I am use VS2005 Profession and SqlExpress 2005 to develop WebApplication. >> I use many SqlDataReader in my application. >> I think there are some connecitons not be closed after reading data. >> >> How can I moniter the status of conneciton of a Database? >> > > I have tried the codes (below) in the link:
But what doesGetDataTableFromSql function come from? What about ths SQL: select spid from master..sysprocesses The code can't compile. I am using VS2005 and Sql2005 Express. //-------------------------------------------------------------------------------------------------------------------- public static void KillProcesses(string DBConStr, string DBName) { try { using (SqlConnection conn = new SqlConnection(DBConStr)) { conn.Open(); string sSql = "select spid from master..sysprocesses where dbid=db_id('" + DBName + "')"; DataTable oTable = GetDataTableFromSql(sSql, DBConStr, 60); foreach(DataRow oRow in oTable.Rows) { int Id = int.Parse(oRow[0].ToString()); SqlCommand cmd = new SqlCommand("kill " + Id, conn); cmd.ExecuteNonQuery(); } conn.Close(); } } catch{} } Show quote "Robbe Morris [C# MVP]" <i***@eggheadcafe.com> ¼¶¼g©ó¶l¥ó·s»D:O6zP3lj7FHA.1***@TK2MSFTNGP12.phx.gbl... > http://www.eggheadcafe.com/forums/ForumPost.asp?ID=46053&INTID=9 > > -- > Robbe Morris - 2004/2005 Microsoft MVP C# > http://www.masterado.net > > > > > > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message > news:uA8LHbj7FHA.1864@TK2MSFTNGP12.phx.gbl... >> 1) Use the SQL Profiler to monitor connections >> 2) With the 1.1 framework (try to) use the .NET data performance >> counters. These can show how many connections are pooled but they don't >> work very well. >> 3) With the 2.0 framework there are new performance counters (that work) >> 4) With any version of SS you can monitor the number of user connections >> using its own performance counters. >> >> I'll be discussing these (and showing the code) in my session at VSLive >> in Sydney. >> >> -- >> ____________________________________ >> 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. >> __________________________________ >> >> "ad" <fly***@wfes.tcc.edu.tw> wrote in message >> news:OFfUJlh7FHA.2456@TK2MSFTNGP10.phx.gbl... >>>I am use VS2005 Profession and SqlExpress 2005 to develop WebApplication. >>> I use many SqlDataReader in my application. >>> I think there are some connecitons not be closed after reading data. >>> >>> How can I moniter the status of conneciton of a Database? >>> >> >> > > That is an old function of ours that just uses a sqldataadapter
to fill a DataTable. The key for you is to look at the two different sql statements in strings and adapt it to what you need to do. Show quote "ad" <a*@wfes.tcc.edu.tw> wrote in message news:OlsmkBl7FHA.3636@TK2MSFTNGP09.phx.gbl... >I have tried the codes (below) in the link: > > But what doesGetDataTableFromSql function come from? > > What about ths SQL: > select spid from master..sysprocesses > > The code can't compile. > > I am using VS2005 and Sql2005 Express. > > //-------------------------------------------------------------------------------------------------------------------- > public static void KillProcesses(string DBConStr, string DBName) > { > try > { > using (SqlConnection conn = new SqlConnection(DBConStr)) > { > conn.Open(); > string sSql = "select spid from master..sysprocesses > where dbid=db_id('" + DBName + "')"; > DataTable oTable = GetDataTableFromSql(sSql, DBConStr, > 60); > foreach(DataRow oRow in oTable.Rows) > { > int Id = int.Parse(oRow[0].ToString()); > SqlCommand cmd = new SqlCommand("kill " + Id, > conn); > cmd.ExecuteNonQuery(); > } > conn.Close(); > > } > } > catch{} > } > > > > > > > "Robbe Morris [C# MVP]" <i***@eggheadcafe.com> > ¼¶¼g©ó¶l¥ó·s»D:O6zP3lj7FHA.1***@TK2MSFTNGP12.phx.gbl... >> http://www.eggheadcafe.com/forums/ForumPost.asp?ID=46053&INTID=9 >> >> -- >> Robbe Morris - 2004/2005 Microsoft MVP C# >> http://www.masterado.net >> >> >> >> >> >> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message >> news:uA8LHbj7FHA.1864@TK2MSFTNGP12.phx.gbl... >>> 1) Use the SQL Profiler to monitor connections >>> 2) With the 1.1 framework (try to) use the .NET data performance >>> counters. These can show how many connections are pooled but they don't >>> work very well. >>> 3) With the 2.0 framework there are new performance counters (that work) >>> 4) With any version of SS you can monitor the number of user connections >>> using its own performance counters. >>> >>> I'll be discussing these (and showing the code) in my session at VSLive >>> in Sydney. >>> >>> -- >>> ____________________________________ >>> 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. >>> __________________________________ >>> >>> "ad" <fly***@wfes.tcc.edu.tw> wrote in message >>> news:OFfUJlh7FHA.2456@TK2MSFTNGP10.phx.gbl... >>>>I am use VS2005 Profession and SqlExpress 2005 to develop >>>>WebApplication. >>>> I use many SqlDataReader in my application. >>>> I think there are some connecitons not be closed after reading data. >>>> >>>> How can I moniter the status of conneciton of a Database? >>>> >>> >>> >> >> > > I'm not recommending that you do this to
close connections in production. That sample could be useful in helping you figure out if you really do have a problem with connections not be closed or not. Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:uA8LHbj7FHA.1864@TK2MSFTNGP12.phx.gbl... > 1) Use the SQL Profiler to monitor connections > 2) With the 1.1 framework (try to) use the .NET data performance counters. > These can show how many connections are pooled but they don't work very > well. > 3) With the 2.0 framework there are new performance counters (that work) > 4) With any version of SS you can monitor the number of user connections > using its own performance counters. > > I'll be discussing these (and showing the code) in my session at VSLive in > Sydney. > > -- > ____________________________________ > 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. > __________________________________ > > "ad" <fly***@wfes.tcc.edu.tw> wrote in message > news:OFfUJlh7FHA.2456@TK2MSFTNGP10.phx.gbl... >>I am use VS2005 Profession and SqlExpress 2005 to develop WebApplication. >> I use many SqlDataReader in my application. >> I think there are some connecitons not be closed after reading data. >> >> How can I moniter the status of conneciton of a Database? >> > >  |
|||||||||||||||||||||||
Other interesting topics