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,568
  • 11
  • 79
  • 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
  • 70,237
  • 8
  • 167
  • 244