February 3, 2005 at 9:11 am
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.
February 3, 2005 at 9:54 am
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.
February 3, 2005 at 9:55 am
Have you tried killing this spid, maybe that's why you can't do anything with the proc?
KILL 55
February 3, 2005 at 9:56 am
Kill 55 returns the Rollback Status message as well. The odd part is that it shows 100% complete and 0 Seconds remaining.
February 3, 2005 at 9:59 am
Try killing the spid repeatedly. If that doesn't help, you might have to re-cycle the services.
February 3, 2005 at 10:14 am
I'll give that a go. Question, though. Is this likely what's preventing me from dropping the proc?
February 3, 2005 at 10:53 am
What's the error message you are getting ?
February 3, 2005 at 10:58 am
No error, just any attempt to touch that sp (exec, drop, alter) from either QA or EM hangs.
February 3, 2005 at 11:30 am
I'm not sure but i believe re-start of services might help.
February 3, 2005 at 11:56 am
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.
February 3, 2005 at 12:06 pm
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