1

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?

2
  • By which job, do you mean SQL Agent job?
    – Tom V
    Feb 12 '16 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... Feb 12 '16 at 11:03
1

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:

SELECT *
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
2
  • By using this query, i'm unable to find the job name which invokes it. it returns 0 rows. Feb 12 '16 at 11:00
  • Then you'll need to check for the other things that I mention in my post. Best of luck! :) Feb 12 '16 at 11:02
0

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)
)
go

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 
begin

    insert into CaptureUnwantedJob
    SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    pr.program_name
    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'

end

-- 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
where 
    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!

5
  • 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. Feb 12 '16 at 10:10
  • Have configured the job with the above code and scheduled on 6:59 AM. Thanks for the answer. Will update Tomm. Feb 12 '16 at 10:59
  • @DanielHutmacher: I've first answered as a guest and only then created an account.
    – sergiu
    Feb 12 '16 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
    Feb 12 '16 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? Feb 16 '16 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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