Post reply

Extended Stored Procedures - xp_cmdshell / Wait Type msql_xp

  • December 21, 2011 at 4:41 pm

    #250254

    Hello;

    I am a new DBA here. About once a week, I get several processes with wait type msql_xp. I read that this is due to system waiting on an extended store proc. These processes and up hanging sometimes over 24 hours until I restart the SQL Server. We have many jobs running that contain extended stored procedures like xp_cmdshell.

    My questions are; Is there any way to pin point what sp is causing the issue? Or could this be due to many of the extended sp running at once and they are bumping into each other? Is there any logging?

  • December 21, 2011 at 10:48 pm

    #1424571

    When an xp_cmdshell job executes, two spids are active... one for the "calling proc" and the other for the xp_cmdshell call.

    If the xp_cmdshell job fails for any reason, the spid for the "calling proc" cannot be killed until the spid for the xp_cmdshell job is killed. Finding the spid for the xp_cmdshell job is more of an art than a science.

    The absolute best thing to do is (as with any code) make the code more bullet proof and to have it exit properly when an error is detected.

    Yeah... it's going to take some work but it'll be worth it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply