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

Derek Downey
  • 23,440
  • 11
  • 78
  • 104
David Mathis
  • 377
  • 1
  • 5
  • 12

1 Answers1

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.

gbn
  • 69,809
  • 8
  • 163
  • 243
  • 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 Sep 08 '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 08 '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 Mathis Sep 13 '11 at 18:15