November 18, 2010 at 10:02 am
Background
I've got an SP (call it "A") that calls an executable via xp_cmdshell [no lectures on xp_cmdshell, please 😀 ].
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).
Problem
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".
Questions
(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.
November 25, 2010 at 8:07 pm
Most likely the command launched by xp_cmdshell has some problems executing on one of your sites.
It may be access privileges, enviromental variables, anything else.
Command may be just waiting for Y/N confirmation, which, of course, never comes.
Login to the machine under the account which runs SQL Server and try to run the shell command from the command prompt.
It must show you where the problem is.
_____________
Code for TallyGenerator
November 29, 2010 at 8:32 am
It turns out this worked: the developer took out capturing the return value from xp_cmdshell (e.g., removing @retval = xp_cmdshell... and instead just calling xp_cmdshell... ).
Thank you for the troubleshooting tip - I'll ask the developer if he had tried that or not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply