SQL Server 查找正在运行过程的作业 [英] SQL Server Find What Jobs Are Running a Procedure

问题描述

有没有办法找出哪些作业正在使用某个存储过程?

Is there a way to find out what jobs are using a certain stored procedure?

推荐答案

这将捕获在作业步骤中显式引用过程的实例:

This will capture instances where the procedure is explicitly referenced in the job step:

SELECT j.name 
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS 
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%procedurename%'
  );

如果它被从作业调用的其他东西调用，或者命令是用动态 SQL 构造的，这可能会更难以追踪.另请注意，如果您的过程名称也可以自然地出现在其他代码、注释等中，则可能会产生误报.

If it is called by something else that is called from the job, or the command is constructed with dynamic SQL, this might be a little more difficult to track down. Note also that if your procedure name can also appear naturally in other code, comments, etc. that it may produce false positives.

这篇关于SQL Server 查找正在运行过程的作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！