Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
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: good idea. –  gbn Sep 8 '11 at 17:48
    
@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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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