Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
 Posted - 2012-08-16 : 10:26:55
|
| Hi,I came across this problem again:I have a curious issue with an SP. I call it in a Job every minute, last night there was a fairlure while executing the SP and maybe it stopped in a wrong status. Now I'm trying to execute this SP and it never ends the execution, so I copy all the code inside the SP and create a new one with another name and all works fine with this new one.My question is, what happened with the old SP?, why it never executes again? it there any way to see why this SP is 'blocked'?If I execute the SP with recompile option all works fine. I can see the blocked execution is in RUNNABLE status, if I try to KILL/ROLLBACK the command changes to KILLED/ROLLBACK but it continues workin in the background.If I launch the same SP again I can see in the proceesses list a new one with LCK_M_X wait type.Last time I didn't find a solution, so I created a new SP with a new name and started using this.Any idea?ThanksThe Padrón peppers itch and other don't |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-16 : 11:12:23
|
| Does the SP do anything odd - xp_cmdshell, send email global temp tables etc?It sounds like the SP didn't finish from the previous run and was probably waiting for something external. Could just be that something else is stopping it getting an execution plan.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
Posted - 2012-08-16 : 11:14:13
|
| The SP is accesing to a external linked Oracle database. It's waiting for days...The Padrón peppers itch and other don't |
|
|
|
|
|
|
Locked Stored Procedure