| Author |
Topic |
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
 Posted - 2009-10-27 : 04:51:17
|
| Hi everybody,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'?thanks a lot.The Padrón peppers itch and other don't |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-27 : 09:35:40
|
| Try SP_WHO2 and look for blocking.Mike"oh, that monkey is going to pay" |
 |
|
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
Posted - 2009-10-27 : 10:24:48
|
| I've executed the SP_WHO2 and there's no BLOCKED Status in any record.I think that the one failing is in RUNNABLE status.Thanks.The Padrón peppers itch and other don't |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-27 : 10:30:56
|
| hmm. Interesting. What happens if you try to execute it with recompile?Mike"oh, that monkey is going to pay" |
|
|
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
Posted - 2009-10-27 : 10:35:37
|
| With the recompile option it works!!But if i try again without recompile it doesn't.The Padrón peppers itch and other don't |
|
|
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
Posted - 2009-10-27 : 10:58:28
|
| I've done a kill query with the spid but it's still on runnable status but now the command is KILLED/ROLLBACK.The Padrón peppers itch and other don't |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-27 : 11:03:56
|
| Take the query below and run it. Try to filter it down in the "text like" clause so you just get your proc back. This is a good one.SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 AND Objtype='Proc'AND Text like 'Create Procedure%'ORDER BY usecounts DESC;Mike"oh, that monkey is going to pay" |
|
|
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
Posted - 2009-10-28 : 03:56:50
|
| Thanks for your help,I've executed the code you've sent me. I don't understand the pourpose of this code, the output is a list of some of the SP I have in the Db and other System SP but the one I'm trying to recover it is not in the list.Update : this morning I had the chance to restart the server for installing windows updates and the SP is still failing. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-28 : 09:32:31
|
| please post the output for the rows related to your spMike"oh, that monkey is going to pay" |
|
|
|
cidmi.dovic
Yak Posting Veteran
53 Posts |
Posted - 2010-02-11 : 09:28:56
|
quote:
Originally posted by mfemenel
please post the output for the rows related to your spMike"oh, that monkey is going to pay"
There are no entries for it.The Padrón peppers itch and other don't |
|
|
|
|
Problem with an Stored Procedure