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 cmd process started from SQL Server Agent

Hi!
I have a small problem , but it's still a problem.
I have a SQL Server Agent job that runs a .cmd file. This CMD is logged to a textfile.
This process is locked, waiting for me to type a password, but I have nowhere to type that pass.

What I want to do is kill the process that i locking the logfile, because since the logfile is locked, the job cannot be started again (and it's a scheduled job).
The jobs status is 'Not Running'.
I have solved the problem by making the cmd write to another logfile, so the schedule will work, but the file is still locked, and I don't want to restart the server since it's a productionserver.

How to I find the process that is initialized from SQL Agent, and kill it?

Thanks!

BixCould be you'll have a big problem...

what does the cmd file execute?

If it's ANY type of GUI you could hang the box...

What's in the cmd file?|||Nope, no GUI is executed.
It's juat a matter of reading textfiles, formatting the data and inserting it into the db.

The part that hangs is a "Net Use"-command for accessing a networkshare.
I have added the user and pass so that this does not happen again...|||I'm not sure about this, but I believe you will find cmdexec in your system processes. You can kill the PID and it should take care of you.|||killing the parent process without terminating the child may lead to system instability. cmdexec does not take care of anything that had been invoked from it.