Post reply

How to find the which JOB and JOB-STEP is running for particular Stored Procedure

  • March 14, 2011 at 9:51 am

    #233839

    I am having a stored procedure,and i want to find which job is executing that particular SP....plz any suggestions

  • March 14, 2011 at 10:50 am

    #1298448

    USE msdb

    GO

    DECLARE @proc_name SYSNAME = 'stored_procedure_name' ;

    SELECT *

    FROM dbo.sysjobsteps

    WHERE command LIKE '%' + @proc_name + '%' ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 14, 2011 at 11:01 am

    #1298453

    select s.name, j.command from msdb.dbo.sysjobsteps j

    inner join msdb.dbo.sysjobs s

    on s.job_id=j.job_id

    where command like '%sqlbackup%'

  • March 14, 2011 at 11:01 am

    #1298454

    The above will work if it's in the command of the jobstep. If it's buried in SSIS, you're SoL. You can try a trace to track when it's being called to help narrow the possibilities down.

    Also, you'll need to run this on any machine that has a linked server to your machine, as you could call it foreignly.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 14, 2011 at 11:42 am

    #1298488

    Craig Farrell (3/14/2011)

    The above will work if it's in the command of the jobstep. If it's buried in SSIS, you're SoL. You can try a trace to track when it's being called to help narrow the possibilities down.

    Also, you'll need to run this on any machine that has a linked server to your machine, as you could call it foreignly.

    You can import the SSIS package into BIDS and save it to your file system. It saves as a dstx file, which is an XML file. You can then search it for the name of the stored procedure.

    Good point about the linked server. I think I would only go that route after other searches didn't bear out.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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