Post reply

Is this a procedure call?

  • October 20, 2009 at 12:50 pm

    #142582

    Hello,

    I'm running a variation of the sp_who procedure to list processes, and one process in particular is of interest. It has been periodically appearing as the head of a blocking chain, and it starts with CREATE PROC .... Basically it appears to be the text of that procedure's definition. It has a very short definition (one SELECT statement) yet when it causes blocking I see that it has been in SUSPENDED status for hours.

    My question is, does this mean the procedure is being called, and as a result its definition is listed? Or is some process actually trying to execute the CREATE PROCEDURE command?

    A secondary question is how such a short procedure can be suspended for so long, and I would be happy to hear advice or possible answers, but I can also ask that in a separate forum post as well.

    Thanks in advance for any help!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • October 20, 2009 at 12:52 pm

    #1068447

    I would think this is the DDL to create a procedure. If it's suspended, maybe the network link got shattered and SQL didn't close it out properly? Can you kill it?

  • October 20, 2009 at 1:33 pm

    #1068480

    Steve Jones - Editor (10/20/2009)

    I would think this is the DDL to create a procedure. If it's suspended, maybe the network link got shattered and SQL didn't close it out properly? Can you kill it?

    Hi Steve,

    Thanks for your response!

    Yes, we are killing it at the moment, but I'm not sure what is calling this DDL. And if possible I would like to know why it needs to be called or whether we can just stop it from running in the first place. It is blocking our maintenance plans and causing timeout errors in the application when it runs.

    Also, from your response it sounds like this is DDL and not a procedure call (which I guess I now realize would look like 'EXEC ProcName' not 'CREATE PROC ProcName...'). Is that correct?

    I'll need to dig further, as I don't know of any reason why the same CREATE PROCEDURE command should be running every few days (if not more frequently - I will need to profile it to see how often it's running).

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • October 20, 2009 at 1:57 pm

    #1068499

    Check the default trace. That should tell you who sent it in

Viewing 4 posts - 1 through 3 (of 3 total)

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