Post reply

Detecting any specific stored procedure is running

  • July 12, 2007 at 3:05 pm

    #120745

    Hi all,

    Is there any machanism that I can detect in my system that a/any stored procedure is running at a given point of time (not from the application end).

    Or

    If I want to check whether the stored procedure is running or not, is there a way to find that?

    Thanks much...

     


    Zaheer

  • July 12, 2007 at 4:02 pm

    #719126

    You can create a custom profiler only for Stored Procedures which will tell you when a particular stored procedure will get called and finishes.

    Tushar

  • July 12, 2007 at 4:13 pm

    #719131

    Thanks for your email.

    this probably means the profiler trace should be running during the time when I want to check the SP running.

    is anyother way (like, query, feature, etc)

    thanks again...


    Zaheer

  • July 12, 2007 at 7:54 pm

    #719152

    I would alter the sp to "execute as" a "testlogin". Then I would filter the trace for "testlogin" loginname and events: sp:starting; sp:completed. You can leave such a profiler trace to run for >24 hours as it will not trace anything else that your sp.

     

  • July 12, 2007 at 11:36 pm

    #719173

    You can query the sysprocess table with dbcc inputbuffer(spid) to find the procedure is running or not.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

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

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