I'm unable to track a stored procedure that is executing every morning 7 AM. Is there is any script to check that which job invokes that particular SP?

  • By which job, do you mean SQL Agent job?
    – Tom V
    Commented Feb 12, 2016 at 10:01
  • Yes, i have many jobs that is configured in my SQL Server. Need to find out which jobs calls a particular SP. For Ex: i have a SP named "USER_SP_FIND", i need find out which jobs invokes this... Commented Feb 12, 2016 at 11:03

2 Answers 2


The SQL Server Agent job definitions are stored in the msdb database, in the sysjobs and sysjobsteps tables. You could do a plaintext search on those tables with the following query:

FROM msdb.dbo.sysjobsteps
WHERE subsystem='TSQL' AND
      command LIKE '%nameofmyprocedure%'

Note that there are still cases when your stored procedure still won't show up.

  • If there's another stored procedure run by the job that, in turn, launches the stored procedure you're looking for
  • If an SSIS package launches the stored procedure
  • If the procedure isn't launched by SQL Server Agent, but by some other scheduler task
  • By using this query, i'm unable to find the job name which invokes it. it returns 0 rows. Commented Feb 12, 2016 at 11:00
  • Then you'll need to check for the other things that I mention in my post. Best of luck! :) Commented Feb 12, 2016 at 11:02

Create a new table such as this:

create table CaptureUnwantedJob
SQL_Command nvarchar(max),
SPID int,
Status nvarchar(125),
Command nvarchar(255),
ProgramName nvarchar(500)

Create a new Agent job that will start at 6:59 AM. Place this query in it:

truncate table CaptureUnwantedJob

declare @i int = 1

while @i <= 10 

    insert into CaptureUnwantedJob
    SELECT sqltext.TEXT,
    FROM sys.dm_exec_requests req
    join sys.sysprocesses pr on pr.spid = req.session_id
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext

    set @i+=1

    -- wait another 10s before capturing again
    waitfor delay '00:00:10'


-- analyze the captured spids based on the PrgramName column -> it should look like something like this:
-- SQLAgent - TSQL JobStep (Job 0xF4CBD0F6534D9D49B28E15E15138ABD7 : Step x)
select * from CaptureUnwantedJob
    ProgramName like '%SQLAgent%'

Btw, you will need highly elevated rights - I believe part of sysadmin role - in order to be able to query the above 3 tables.

Next - I don't know of any script that would do that, but here you have to do some manual work - once you go the Job that runs that sp, it will be in the format of eg: 0xF4CBD0F6534D9D49B28E15E15138ABD7 - get the last 32 chars and place the '-' accordingly so that you will get a valid uniqueidentifier -> the result will be the job_id you are looking for:

select * 
from msdb.dbo.sysjobs 
where job_id = convert(uniqueidentifier,'F6D0CBF4-4D53-499D-B28E-15E15138ABD7')

Good luck!

  • Sergiu, if you've lost your account details or accidentally registered a duplicate account, you can merge your accounts. This way, you'll be able to edit your posts more easily. Commented Feb 12, 2016 at 10:10
  • Have configured the job with the above code and scheduled on 6:59 AM. Thanks for the answer. Will update Tomm. Commented Feb 12, 2016 at 10:59
  • @DanielHutmacher: I've first answered as a guest and only then created an account.
    – sergiu
    Commented Feb 12, 2016 at 12:34
  • @DanielHutmacher: I think I know what you ment. I just submitted the merge request form. Thanks for pointing that for me, I have no clue how I ended up having two accounts with the same username and email.
    – sergiu
    Commented Feb 12, 2016 at 12:51
  • The above job din't capture the SP execution details which i want. Later on i went for tracing that SP sharp at 7 AM. I found that it was executed through linked Server. Is there is any way trace from which server it was executed? Commented Feb 16, 2016 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.