Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_cmdshell and DBCC FREEPROCCACHE


xp_cmdshell and DBCC FREEPROCCACHE

Author
Message
Jed S Wilson
Jed S Wilson
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 181
Background
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).

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.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6670 Visits: 11567
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.
Jed S Wilson
Jed S Wilson
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 181
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search