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