Need Script to find SP Invoked in SQL

Question

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?

Answer 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

Answer 2

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!