| Author |
Topic |
|
anitha2324
Starting Member
18 Posts |
 Posted - 2008-04-17 : 11:04:37
|
| Dear All , I am developing some java applications , using sql server 2000 as my database , sometimes i used to open the connections to the database and i used to forget to close it from such applications , i want to know how the number of open connections to the database i need it urgent |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-17 : 11:30:09
|
| sp_who2 active |
 |
|
|
anitha2324
Starting Member
18 Posts |
Posted - 2008-04-17 : 11:45:53
|
| hi thanks for your reply , but i dont think so this is shown the exact open connections , because it is shown so many connections to master db , and shown only one connection to my created db "TestGeneral" , inspite that i have more then one connection to the db "TestGeneral" ????????so why it is shown only one connection |
|
|
|
craig79
Starting Member
33 Posts |
Posted - 2008-04-17 : 16:06:14
|
| If u use sp_who2 it will show u all other connections too.But they may not be active.since u executed sp_who2 active it showed u only a single active connection. |
|
|
|
anitha2324
Starting Member
18 Posts |
Posted - 2008-04-23 : 03:38:00
|
| Hi All,Thanks for your replys , now i want to kill all the connections which are not active ( which are sleeping )is it possible to do that using sql statement delete from sp_who2where status='sleeping'I have tried the above statement but it fails !!!!!! , anyhow i want to moniter all the database connections and let the active to run and kill the non active connections , can any one tell me how i can achive that |
|
|
|
craig79
Starting Member
33 Posts |
Posted - 2008-04-23 : 16:52:25
|
| Anitha,Please DO NOT delete all connections which are sleeping.There are some system connection (spid's) present which keep running in the background.Well u can Kill those spid's related to a particular DB.Below just Replace the DBName with the database name u wish to kill the connections for.SELECT 'KILL ' + CONVERT (char(3),spid) FROM dbo.sysprocesseswhere dbid = (select db_id('DBName'))and status = 'sleeping'GOPaste the output that you get from this query in the query analyser and then fire all the KILL statements.It will kill all the connections to that database which are sleeping.Or else if you need to kill a particular spid use following.KILL spidgo |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 16:57:10
|
| Just because a connection is sleeping doesn't mean you should kill it. It could be ready to run another query in just one more second. Killing sleeping connections shows that you are not ready for sysadmin power.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-23 : 22:20:44
|
| In fact, you should disconnect from client side. |
|
|
|
anitha2324
Starting Member
18 Posts |
Posted - 2008-04-25 : 04:15:19
|
| Hi All, thanks for all the replys , i have exeucete the query provided by craig79 and it is working fine , but tkizer said we should not kill such sleeping connections , so what is the optimal solution as i said before I have some applications in java which make the connection to the sql database sometimes such java applications will cause some exception and such application will be terminated (befoe reaching to the statement which close the databse connection ) leaving the database connection opened , once the application starts again it will open new connection without closing the previous one and use the new connection .this is for one application but if i have more then one application , so imagine how much connections will not be closed in case of exceptions.for me i have got idea ( using the sql provided by craig79) to kill such sleeping or inactive ( is there something called inactive connection , if so what is the difference between sleeping and inactive) to write application which uses the sql provided by craig to kill such sleeping connections , is my idea is the optimal one or there is better way to do that which makes the database works fine without hangup |
|
|
|
anitha2324
Starting Member
18 Posts |
Posted - 2008-04-28 : 14:33:45
|
| Hi All, I am still waiting reply for my last post , is there any one can help me for that .... . PLz |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-28 : 14:35:36
|
| So your application raises an exception and terminates. I don't know java but don't you code up some kind of error handling which would allow a soft close? If anything I write simply causes a fatal and doesn't proactively clean things up, I'd be in a world of trouble. Just curious....Terry |
|
|
|
anitha2324
Starting Member
18 Posts |
Posted - 2008-04-28 : 15:06:21
|
| Hi thanks for your reply , ok i have already handled such exceptions but if the the server (in which the java applications are running ) is restarted for some reasons (like power failure ...etc ) so in this case it will leave the connections opens , once the server restarded such applications will be restarted again without closing the previous connections so how to kill those inactive connections and is there difference between inactive connections and sleeping connections ... |
|
|
|
|
no of current connections