SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 no of current connections
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anitha2324
Starting Member

18 Posts

Posted - 04/17/2008 :  11:04:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/17/2008 :  11:30:09  Show Profile  Reply with Quote
sp_who2 active
Go to Top of Page

anitha2324
Starting Member

18 Posts

Posted - 04/17/2008 :  11:45:53  Show Profile  Reply with Quote
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
Go to Top of Page

craig79
Starting Member

India
33 Posts

Posted - 04/17/2008 :  16:06:14  Show Profile  Reply with Quote
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.
Go to Top of Page

anitha2324
Starting Member

18 Posts

Posted - 04/23/2008 :  03:38:00  Show Profile  Reply with Quote
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_who2
where 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



Go to Top of Page

craig79
Starting Member

India
33 Posts

Posted - 04/23/2008 :  16:52:25  Show Profile  Reply with Quote
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.sysprocesses
where dbid = (select db_id('DBName'))
and status = 'sleeping'
GO

Paste 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 spid
go


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 04/23/2008 :  16:57:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/23/2008 :  22:20:44  Show Profile  Reply with Quote
In fact, you should disconnect from client side.
Go to Top of Page

anitha2324
Starting Member

18 Posts

Posted - 04/25/2008 :  04:15:19  Show Profile  Reply with Quote
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

Go to Top of Page

anitha2324
Starting Member

18 Posts

Posted - 04/28/2008 :  14:33:45  Show Profile  Reply with Quote
Hi All,

I am still waiting reply for my last post , is there any one can help me for that .... . PLz
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 04/28/2008 :  14:35:36  Show Profile  Reply with Quote
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
Go to Top of Page

anitha2324
Starting Member

18 Posts

Posted - 04/28/2008 :  15:06:21  Show Profile  Reply with Quote
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 ...



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000