5

I have a SQL Server 2005 Std (x64) with SP4 that has a procedure I cannot seem to kill. If I view sys.dm_exec_requests I see SPID 103 with wait_type of LCK_M_SCH_M, a status of SUSPENDED and command of KILLED/ROLLBACK. However the SPID doesn't go away. I can even do sp_who 103 and I see the SPID with KILLED/ROLLBACK. The SPID has a current wait_time of almost 20 hours since I killed it.

Also, running KILL 103 WITH STATUS ONLY returns 0% completed

Improve this question

1 Answer 1

Reset to default
6

Sometimes a SPID can choke for some out of process calls.

  • xp_sendmail (the old way)
  • sp_OA%
  • sp_xmlpreparedocument
  • etc

You need to stop SQL Server or even reboot the server

Or more likely you could be rolling back a huge UPDATE or such: wait or restart, up to you...

Edit: Aaron's comment of changing a DB status may work as well as an intermediate step.

Improve this answer
3
  • Okay, its just a small stored procedure not using a transaction.. not sure what hung it up so bad. I'll have to wait till my next maintenance cycle to restart. Thanks
    – David Mathis
    Commented Sep 8, 2011 at 17:02
  • 4
    If the SPID is connected to a specific database, you may be able to avoid a restart by setting that database OFFLINE or SINGLE_USER - WITH ROLLBACK IMMEDIATE and then set the database back ONLINE or MULTI_USER. Might be worth a try if you can afford to blip the database for a moment but not the entire instance.
    – Aaron Bertrand
    Commented Sep 8, 2011 at 17:24
  • @Aaron Bertrand: Yeah, I tried setting to SINGLE_USER and OFFLINE with ROLLBACK IMMEDIATE, but it just hangs and I have to stop the query. Thanks for the thought though!
    – David Mathis
    Commented Sep 13, 2011 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.