Unable to kill SPID

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

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.

StackExchange Database Administrators Q#5535, answer score: 6