Having killed a SPID, it still exists, how do I get rid of it?
-
As the subject suggests, I have killed a SPID, however it still remains when I run an sp_who2.
Its status is runnable and the command is: KILLED/Rollback
Trying to kill the process again returns the following error:
SPID 1427: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
Any ideas how I can kill this off completely without a reboot?
Thank in advance.
-
What was the process doing before you killed it? If it was a SELECT then you may need to cycle the sql service not the entire box.
If it was another type process i.e. UPDATE/DELETE/INSERT then you will need to wait for it to go away by itself. You may be able to help it by killing the PC connection that initiated the process as well. I assume that this was a rogue ran from somewhere other than the server.
You may wind up cycling anyway.......
Good Luck
Good Hunting!AJ Ahrens
webmaster@kritter.net -
also is it SQL server 7?
this is a common bug in SQL server 7 (look up SQL server orphaned process on http://www.micrososft.com)
if it is an orphaned process then the only way to clear it is a stop and restart of the service.
MVDBA
-
If the killed SPID was doing a lot of updates/inserts, then I'd say wait for it to finish rollback. SQL needs to keep the integrity of it's database intact so it will always rollback a killed task. I don't know how long it will take to rollback, but I guess it could take as long at the cancelled task was running before you killed it (providing what the original task was doing of course).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply