Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

xp_cmdshell and DBCC FREEPROCCACHE Expand / Collapse
Author
Message
Posted Thursday, November 18, 2010 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:42 PM
Points: 6, Visits: 181
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.
Post #1023033
Posted Thursday, November 25, 2010 8:07 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
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.
Post #1026608
Posted Monday, November 29, 2010 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:42 PM
Points: 6, 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.
Post #1027379
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse