kill spid never completing
-
We are running SQL2000 SP3 and have a process that seems to get hung up to the point that we can't even view current activity from within EM.
I'm having to query sysprocesses to find the spid id to kill. When I issue the kill command, we get the following message:
SPID xxx Transaction rollback in progress. Estimated rollback completion: 00% Estimated 33 Seconds.
After a number of minutes we issue kill xxx statusonly and the same message appears...no progress.
The only way we can get the process out is to reboot the server.
Any ideas?
-
No, I would suggest you wait for it to finish.
If a transaction was running for an hour, and you killed it, it would take about an hour (generally speaking) to rollback. As long as its showing a rollback, it is probably doing something.
If you were to reboot now, it will cause sql server to do a recover on startup, and that could take a while also.
-
Please check DBCC Inputbuffer(<SPID>
and see what this SPID was trying to do.If you see something like xp_cmdshell or some other OS call, then you may see "KILLED/ROLLBACK"
Rebooting should not harm anything but as pointed by Ray that it may take time to recover if its doing "real" rollback
-
we don't think it's doing a real rollback...reboot is very quick
-
Would you mind checking DBCC INPUTBUFFER(SPID) to prove the theory pf "real" rollback
-
the developer has been checking their process and feels that the only updates were to a temporary table and changing their processes. hopefully it won't happen again. I'll try the dbcc next time. thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply