Discussion:
Cannot kill Spid
(too old to reply)
Paw Boel Nielsen
2003-12-08 11:22:35 UTC
Permalink
After executing a command which caused a catastropic error on a sql 7 box
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still active
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the server is
not an option...)

Cheers, Paw
Jacco Schalkwijk
2003-12-08 11:39:14 UTC
Permalink
You're stuck with the spid until you restart SQL server if you can't kill
it. I assume it isn't holding any locks (check with sp_lock <spid>), so it
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7 box
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still active
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the server is
not an option...)
Cheers, Paw
Paw Boel Nielsen
2003-12-08 11:48:16 UTC
Permalink
It is indeed holding database locks on the master database and another
database, reason this is a problem is that I need to drop the database that
is being locked by the spid - can I 'force' dropping the database even
though this spid is locking it?
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't kill
it. I assume it isn't holding any locks (check with sp_lock <spid>), so it
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7 box
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Anthony Faull
2003-12-08 14:36:38 UTC
Permalink
You can close all connections to a database by use the Detach Database
dialog. Here's how to do it.

In enterprise manager:
1. Right click on the database name and choose All Tasks and Detach
Database.
2. Click the Clear button to close all connections.
3. Click the Cancel button so that you don't actually detach the db.
Post by Paw Boel Nielsen
It is indeed holding database locks on the master database and another
database, reason this is a problem is that I need to drop the database that
is being locked by the spid - can I 'force' dropping the database even
though this spid is locking it?
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't kill
it. I assume it isn't holding any locks (check with sp_lock <spid>), so it
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Tibor Karaszi
2003-12-08 14:35:28 UTC
Permalink
My guess is that this in the end only executes an ALTER DATABASE command to
set it to single user with appropriate ROLLBACK options.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Anthony Faull
You can close all connections to a database by use the Detach Database
dialog. Here's how to do it.
1. Right click on the database name and choose All Tasks and Detach
Database.
2. Click the Clear button to close all connections.
3. Click the Cancel button so that you don't actually detach the db.
Post by Paw Boel Nielsen
It is indeed holding database locks on the master database and another
database, reason this is a problem is that I need to drop the database
that
Post by Paw Boel Nielsen
is being locked by the spid - can I 'force' dropping the database even
though this spid is locking it?
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't
kill
Post by Paw Boel Nielsen
Post by Jacco Schalkwijk
it. I assume it isn't holding any locks (check with sp_lock <spid>),
so
Post by Anthony Faull
it
Post by Paw Boel Nielsen
Post by Jacco Schalkwijk
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the
spid
Post by Paw Boel Nielsen
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Paw Boel Nielsen
2003-12-08 15:27:16 UTC
Permalink
Thanks for your suggestion but this functionality is only available to sql
2000 not sql 7, I have tried using EM from a sql 2000 machine but 'Detach
Database' option is not enabled when dealing with 7.0 databases.

Paw
Post by Anthony Faull
You can close all connections to a database by use the Detach Database
dialog. Here's how to do it.
1. Right click on the database name and choose All Tasks and Detach
Database.
2. Click the Clear button to close all connections.
3. Click the Cancel button so that you don't actually detach the db.
Post by Paw Boel Nielsen
It is indeed holding database locks on the master database and another
database, reason this is a problem is that I need to drop the database
that
Post by Paw Boel Nielsen
is being locked by the spid - can I 'force' dropping the database even
though this spid is locking it?
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't
kill
Post by Paw Boel Nielsen
Post by Jacco Schalkwijk
it. I assume it isn't holding any locks (check with sp_lock <spid>),
so
Post by Anthony Faull
it
Post by Paw Boel Nielsen
Post by Jacco Schalkwijk
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the
spid
Post by Paw Boel Nielsen
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Tibor Karaszi
2003-12-08 11:44:29 UTC
Permalink
... also, it is worth checking the STATUSONLY option to the KILL command to
see if there's a rollback in progress.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't kill
it. I assume it isn't holding any locks (check with sp_lock <spid>), so it
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7 box
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Paw Boel Nielsen
2003-12-08 11:54:18 UTC
Permalink
I don't think with STATUSONLY is available for sql 7.0, can i check if a
rollback is in progress on sql 7.0?
Post by Tibor Karaszi
... also, it is worth checking the STATUSONLY option to the KILL command to
see if there's a rollback in progress.
--
Tibor Karaszi, SQL Server MVP
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Tibor Karaszi
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't kill
it. I assume it isn't holding any locks (check with sp_lock <spid>), so it
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Tibor Karaszi
2003-12-08 12:23:32 UTC
Permalink
Post by Paw Boel Nielsen
I don't think with STATUSONLY is available for sql 7.0,
Correct. Sorry, I missed the 7.0 part. You can typically see this by
checking I/O against the disks?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Paw Boel Nielsen
I don't think with STATUSONLY is available for sql 7.0, can i check if a
rollback is in progress on sql 7.0?
"Tibor Karaszi"
Post by Tibor Karaszi
... also, it is worth checking the STATUSONLY option to the KILL command
to
Post by Tibor Karaszi
see if there's a rollback in progress.
--
Tibor Karaszi, SQL Server MVP
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Paw Boel Nielsen
Post by Tibor Karaszi
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't
kill
Post by Tibor Karaszi
Post by Jacco Schalkwijk
it. I assume it isn't holding any locks (check with sp_lock <spid>),
so
Post by Paw Boel Nielsen
it
Post by Tibor Karaszi
Post by Jacco Schalkwijk
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the
spid
Post by Tibor Karaszi
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Jacco Schalkwijk
2003-12-08 12:03:24 UTC
Permalink
If there's a rollback in progress you will see "KILLED/ROLLBACK" (can't
remember the exact text) in the results of sp_who and not "AWAITING COMMAND"
as Paw reported. At least that's the case on SQL 2000, is it different on 7?
--
Jacco Schalkwijk
SQL Server MVP
Post by Tibor Karaszi
... also, it is worth checking the STATUSONLY option to the KILL command to
see if there's a rollback in progress.
--
Tibor Karaszi, SQL Server MVP
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Tibor Karaszi
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't kill
it. I assume it isn't holding any locks (check with sp_lock <spid>), so it
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Tibor Karaszi
2003-12-08 12:24:35 UTC
Permalink
Ahh, yes I didn't consider looking at the command, good point, Jacco. I
don't know whether there are any changes between 7.0 and 2000 in this
regard, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Jacco Schalkwijk
If there's a rollback in progress you will see "KILLED/ROLLBACK" (can't
remember the exact text) in the results of sp_who and not "AWAITING COMMAND"
as Paw reported. At least that's the case on SQL 2000, is it different on 7?
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi"
Post by Tibor Karaszi
... also, it is worth checking the STATUSONLY option to the KILL command
to
Post by Tibor Karaszi
see if there's a rollback in progress.
--
Tibor Karaszi, SQL Server MVP
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
Post by Jacco Schalkwijk
Post by Tibor Karaszi
Post by Jacco Schalkwijk
You're stuck with the spid until you restart SQL server if you can't
kill
Post by Tibor Karaszi
Post by Jacco Schalkwijk
it. I assume it isn't holding any locks (check with sp_lock <spid>),
so
Post by Jacco Schalkwijk
it
Post by Tibor Karaszi
Post by Jacco Schalkwijk
won't hurt that much to leave it.
--
Jacco Schalkwijk
SQL Server MVP
Post by Paw Boel Nielsen
After executing a command which caused a catastropic error on a sql 7
box
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
(caused by connecting to a sybase 11 database using merant driver
http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the
spid
Post by Tibor Karaszi
Post by Jacco Schalkwijk
Post by Paw Boel Nielsen
cannot be killed. I have tried the kill command but the spid is still
active
Post by Paw Boel Nielsen
and can be seen when running sp_who status: AWAITING COMMAND.
Any suggestions as to how this spid can be killed? (restarting the
server
Post by Jacco Schalkwijk
is
Post by Paw Boel Nielsen
not an option...)
Cheers, Paw
Loading...