I've got an SP (call it "A") that calls an executable via xp_cmdshell [no lectures on xp_cmdshell, please :-D ].
SP "A" is called by another SP (SP "B"), which is in turn started by a SQL Agent job.
SP "B" runs continuously (i.e., the job is, strangely enough, never supposed to stop).
At only one of my sites, the job hangs regularly after it starts upon reboot; apparently it hangs on the xp_cmdshell call [according to developer's diagnosis]. Per developer, what seems to clear it up is a DBCC FREEPROCCACHE right before the call SP "B" makes to SP "A".
(a) Why is this happening? and
(b) Why only at one site, when all sites have identical databases?
At first I was thinking "what, xp_cmdshell being an XP can't have a compiled query plan" but then I figure it's one of the other SPs whose query plan might be problematic. I've searched on this issue and can't find anything to address it specifically, so my guess is I need to back up a step and look at it more generally - for example, is xp_cmdshell a red herring here?
What is the most helpful way in SQL Server 2000 to look at what SQL is trying to do when that job hangs (because SP "A" and/or "B" hangs)... possibly a profiler trace, but what exactly would I be looking for? Perhaps the answer is staring at me but I can't see it right now.