Unable to Execute, Modify, or Drop SP

  • I have a stored proc on my server that hung during a scheduled execution a couple of weeks ago.  I was able to kill the Job that was hung running it, but since then, I've been unable to touch the proc. 

    Anything that references it hangs, as does any attempt to modify or drop it.  Looking at sp_who2, I see the SPID that was originally hung with a Killed/Rollback status.  If I get a status report on it, it shows as:

    SPID 55: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

    Is this related to the problems with dropping the proc?  If not, is there anything else I can try to get rid of this thing?  This is in a 24x7 high visibility environment, so cylcing the service/server is an absolute last resort.


  • Hate to say but when you have a spid hang like that it many times means a restart will be required. You might try killing the spid again first.

  • Have you tried killing this spid, maybe that's why you can't do anything with the proc?

    KILL 55

  • Kill 55 returns the Rollback Status message as well.  The odd part is that it shows 100% complete and 0 Seconds remaining.


  • Try killing the spid repeatedly. If that doesn't help, you might have to re-cycle the services.

  • I'll give that a go.  Question, though.  Is this likely what's preventing me from dropping the proc?


  • What's the error message you are getting ?

  • No error, just any attempt to touch that sp (exec, drop, alter) from either QA or EM hangs. 


  • I'm not sure but i believe re-start of services  might help.

  • I guess you've already thaught of that but just in case :

    Since you seem to need to have the server run 24/7. Can't you just recreate the sp under another name for the time being, and when you get a planned or unplanned down time, see if you can fix this situation.

  • I've managed that part.  I'm more concerned now as to what might have triggered this, and if it could grow into a larger problem.  I was digging backwards working towards the source, and I did find a few things.

    The SP froze on 1/26 during a 1:30 am execution.  In the Event Log at 1:30 the SQL Server serivce showed several errors and an unexpected restart.  This begs the question, did the SP cause the error, or did the Service restart cause the SP to hang? 


Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply