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

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.

  • 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 George Sep 8 '11 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 Sep 8 '11 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 George Sep 13 '11 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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