Killing all sleeping processes

Hi All,
When I run sp_who2, I see there are many sleeping processes. Instead of
killing one by one, I was wondering if there is any way to kill all
sleeping processes programmatically (MS SQL Server 2000).
Thanks a million in advance.
Best regards,
mamunHi,
It is possible to do. But Killing the sleeping user process may not be a
good idea. A user processes
may be running for 40 minutes and when you check that process may be
sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
Script:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + 'Kill' + convert(varchar(5), spid)
from master.dbo.sysprocesses where status='Sleeping'
exec (@.x)
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
SQL Server MVP
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>|||If the unneeded idle connections are created by applications, then it's an
issue for the developer to resolve by managing the connection pool. If the
connections are created by people logging into Query Analyzer or Enterprise
Manager, then it's an issue for the DBA to resolve by perhaps restricting
logins and permissions.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>

Killing a hung process..

A user connection which was killed went into RUNNABLE status with KILLED/ROL
LBACK cmd when we monitored with SP_WHO2.It looks like the process was hung
and no activity in CPU or I/O.How do we get rid of that process completely w
ithout restarting sql serve
r?Hi,
Normally when you kill a runnable process which does any (Update / Insert /
delete) it does a ROLLBACK to ensure that things are back as old. After
rollback that process will be removed from the process list automatically.
Thanks
Hari
MCDBA
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:AAF19D66-EE57-4C0D-9F84-F5841FF53459@.microsoft.com...

quote:

---

> A user connection which was killed went into RUNNABLE status with

---

KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting sql server?

Killing a hung process..

A user connection which was killed went into RUNNABLE status with KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process was hung and no activity in CPU or I/O.How do we get rid of that process completely without restarting sql server?Hi,
Normally when you kill a runnable process which does any (Update / Insert /
delete) it does a ROLLBACK to ensure that things are back as old. After
rollback that process will be removed from the process list automatically.
Thanks
Hari
MCDBA
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:AAF19D66-EE57-4C0D-9F84-F5841FF53459@.microsoft.com...
> A user connection which was killed went into RUNNABLE status with
KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting sql server?

Killing a hung process

A user connection which was killed went into RUNNABLE status with KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process was hung and no activity in CPU or I/O.How do we get rid of that process completely without restarting the sql server?A spid can get in a state where it can't be killed. In fact some would say
that KILL should be renamed WOUND :-)
One of the more common reasons that a process can'r be killed is if it
called an extended proc or launches an externall process that has some way
hung. Examples include xp_sendmail or xp_cmdshell. This will hang and if you
kill it it will still stay there in sysprocesses until you restart the
server. You can run KILL spid WITH STATUSONLY to get a report of how far
along the rollback is but for these unkillable spids it will report 100%
complete but never be able to kill the spid because it launched an external
process. The only way to get rid of these spids is to restart the SQL
Service however, if they are not holding any locks or keeping a transaction
open, they don't tend to do a lot of harm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:D32E89EF-6D00-423D-81D5-027F5390ACD2@.microsoft.com...
> A user connection which was killed went into RUNNABLE status with
KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting the sql server?sql

Killing a hung process

A user connection which was killed went into RUNNABLE status with KILLED/ROL
LBACK cmd when we monitored with SP_WHO2.It looks like the process was hung
and no activity in CPU or I/O.How do we get rid of that process completely w
ithout restarting the sql s
erver?A spid can get in a state where it can't be killed. In fact some would say
that KILL should be renamed WOUND :-)
One of the more common reasons that a process can'r be killed is if it
called an extended proc or launches an externall process that has some way
hung. Examples include xp_sendmail or xp_cmdshell. This will hang and if you
kill it it will still stay there in sysprocesses until you restart the
server. You can run KILL spid WITH STATUSONLY to get a report of how far
along the rollback is but for these unkillable spids it will report 100%
complete but never be able to kill the spid because it launched an external
process. The only way to get rid of these spids is to restart the SQL
Service however, if they are not holding any locks or keeping a transaction
open, they don't tend to do a lot of harm
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:D32E89EF-6D00-423D-81D5-027F5390ACD2@.microsoft.com...

quote:

---

> A user connection which was killed went into RUNNABLE status with

---

KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting the sql server?

Kill Processes

Can you kill processes in SQL Server 2K without stopping SQL Server and restarting it? I am using sp_who2 to get a list of active users and I see some accounts that are logged off but still showing up and I am trying to find a way to Kill these accounts and the processes they are doing.you can use
1) KILL 53(spid)
Or
Enterprise Manager-->Management-->current Activity-->Process Info
you can pick specific spid and kill it from here..
SS|||What do you mean logged off?

Sleeping?

How many connections are we talking about?

Do you see the Login?

Can you NET SEND them a message?

Better off to leave them alone...It takes resources to reestablish them...

Anyway you'll love this...

It's KILL <spid>|||Do I just do this in the Query Analyer?