none
SPID stuck in KILLED/ROLLBACK state

    Question

  • Hi All,

    SQL Server 2008R2 (10.50.4279) I've noticed a process that hungs, the wait type was "PREEMPTIVE_OS_PIPEOS" and the process itself was a SQL Server Agent job.

    First thing is that in Job Activity monitor I could not see any job running.

    I've tried to kill the spid from Activity monitor but now the SPID is stuck in "KILLED/ROLLBACK" state, and the wait is always "PREEMPTIVE_OS_PIPEOS".

    I've checked that the job executes a stored procedure that invokes sp_send_dbmail with an attachment, I've tried to restart the SQL Server Agent, but the spid is always there.

    How can I kill the process?

    Tuesday, March 31, 2015 10:15 AM

Answers

  • I can't reboot a production server so easily, come on!

    Then you will have to live with it. The wait type PREEMPTIVE_OS_PIPEOPS means that the process is performing an OS call to something related to PIPEOPS. SQL Server employs cooperative multi-tasking. That is, processes yields everyone once in a while to let other other processes run. And that's when they check if they have been killed. But if the process is running an OS call, they are outside the realm of SQL Server and will never see the kill flag.

    What you can try is to see if there is some other activity on the machine that appears to be related to this job. For instance, the process may be running xp_cmdshell and be stuck there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 31, 2015 9:58 PM

All replies

  • Why do you want to kill that process?

    Are you sure that it is not a system process?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, March 31, 2015 10:28 AM
    • Edited by dave_gona Tuesday, March 31, 2015 10:32 AM
    • Proposed as answer by dave_gona Thursday, April 2, 2015 2:02 AM
    Tuesday, March 31, 2015 10:30 AM
  • Hello Ashwin,

    I'm sure this is not a system process, as I said is a SQL Server Agent Job.

    I want to kill it because it was stuck executing since 2 days, normally it completes in few seconds.

    Tuesday, March 31, 2015 10:30 AM
  • just checked and killed the cmd.exe process, I've seen that the process in job activity monitor reported a failure, but the SPID is still there!

    I can't reboot a production server so easily, come on!

    Tuesday, March 31, 2015 10:31 AM
  • I think I have come across similar situation, where a spid which tries to invoke a third party exe gets hung and after that its not possible to kill it.

    We have worked quite a lot on this and the only option was to reboot SQL Server if SPID is not already killed.

    I assume its because the spid itself is in hung state. Sometimes, if the spid is not really in hung state but waiting for the thirdparty application, killing the thirdparty application will immediately allow to kill the spid but most times it just goes into killed/rollback state.

    This was a common scenario while using Tivoli backups, when the backup process hangs.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, March 31, 2015 11:24 AM
  • I can't reboot a production server so easily, come on!

    Then you will have to live with it. The wait type PREEMPTIVE_OS_PIPEOPS means that the process is performing an OS call to something related to PIPEOPS. SQL Server employs cooperative multi-tasking. That is, processes yields everyone once in a while to let other other processes run. And that's when they check if they have been killed. But if the process is running an OS call, they are outside the realm of SQL Server and will never see the kill flag.

    What you can try is to see if there is some other activity on the machine that appears to be related to this job. For instance, the process may be running xp_cmdshell and be stuck there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 31, 2015 9:58 PM
  • Hi,

    my problem was similar. I open, just for fun, notepad via xp_cmdshell.

    xp_cmdshell  'notepad'

    I look via powershell to process on server (admin rights is not needed) and kill my notepadd

    xp_cmdshell  'powershell Get-Process'
    xp_cmdshell  'powershell "Stop-Process -name notepad"'

    I hope it help

    Wednesday, May 16, 2018 9:08 AM