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?
The SQL Server Agent job definitions are stored in the
msdb database, in the
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
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')