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?