Kill Hung Processes 1

[Moebius01]

I have a few processes that were attempting to access linked servers (Oracle in this case) and seem to have hung. The CPU on the db server is now maxed at 100%. I can see the culprit processes, but I'm unable to kill them. Is there any way I can term these processes and regain control of the cpu without killing SQL Server? Strangely enough it's still running fine, just pegging cpu out right now.

 

[Moebius01]

To clarify, I'm trying to kill via enterprise manager, right click and select kill process. I get the confirmation message, but the processes do not terminate.

 

[mrdenny]

Enterprise Manager doesn't refresh very well. Check on the processes in Query Analyser using sp_who2. That should tell you what is actually going on with them. They may be rolling back.

If they are rolling back, in query analyser use the kill command on the spid and it should tell you about how far allong it is.

If they are not rolling back, try killing them from here, you might have better luck.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Moebius01]

Thanks for the tip. I was able to kill all but 1 it seems from QA. The rest now all list as awaiting command, but the one in question lists Killed/Rollback. When I issued the Kill command, it showed 100% Rollback Completion and 0 seconds remaining.

Any gueses?

 

[mrdenny]

Yep. The client that started the request is no longer connected to the SQL Server, so it is unable to pass the error message back to the client, so the rollback can not complete. Unfornitually you will need to restart the SQL Server Service to clear this process.

Denny

--Anything is possible. All it takes is a little research. (Me)