SQL Server作业并发检查异常：自动执行失败但SSIS手动运行正常

问题背景 #

两个SQL Server作业共享同一张表，为避免锁表阻塞，设置其中一个作业每15秒运行一次，运行前先检查是否有其他作业正在执行。但该作业自动执行时，无论是否有其他作业在运行，都不会执行存储过程；将完全相同的查询在SSIS中手动运行却能正常执行。

涉及代码 #

作业中执行的完整查询 #

declare @return_value int,
    @messagesUpdated int,
    @dlrsDeleted int
    
if not exists ( 
    select 1
    from msdb.dbo.sysjobs_view job 
    inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
    where 
        activity.run_Requested_date is not null 
    and activity.stop_execution_date is null 
    )
    
begin
exec @return_value = [dbo].[updateDeliveryReceipts]
    @messagesUpdated = @messagesUpdated OUTPUT,
    @dlrsDeleted = @dlrsDeleted OUTPUT
end
select  @messagesUpdated as N'@messagesUpdated',
    @dlrsDeleted as N'@dlrsDeleted'

select  'Return Value' = @return_value

单独验证正常的作业检查语句 #

if not exists (  
    select 1
    from msdb.dbo.sysjobs_view job 
    inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
    where 
        activity.run_Requested_date is not null 
    and activity.stop_execution_date is null 
)

环境信息 #

• SQL Server 2017 Standard版本
• 运行于Windows Server系统

问题分析与解决方法 #

核心原因 #

作业自动执行时，当前作业本身会被sysjobactivity记录为正在运行，导致not exists条件不成立，因此永远不会进入begin...end块执行存储过程。而手动在SSIS中运行时，当前执行的查询不会被记录到sysjobactivity中，所以检查逻辑正常生效。

解决方案 #

修改检查语句，排除当前作业本身的运行记录：

1. 先获取当前作业的job_id（可在作业属性中查看，或通过msdb.dbo.sysjobs查询）
2. 在where条件中添加排除当前作业的过滤规则

修改后的检查逻辑示例：

if not exists ( 
    select 1
    from msdb.dbo.sysjobs_view job 
    inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
    where 
        activity.run_Requested_date is not null 
    and activity.stop_execution_date is null 
    and job.job_id <> 'XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' -- 替换为当前作业的job_id
    )

额外优化建议 #

• 可通过系统函数动态获取当前作业ID，避免硬编码：

  declare @currentJobId uniqueidentifier
  select @currentJobId = job_id from msdb.dbo.sysjobs where name = '当前作业的名称' -- 替换为实际作业名称
  
  if not exists ( 
      select 1
      from msdb.dbo.sysjobs_view job 
      inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
      where 
          activity.run_Requested_date is not null 
      and activity.stop_execution_date is null 
      and job.job_id <> @currentJobId
      )
  

• 考虑使用applock（应用程序锁）替代作业状态检查，这种方式更可靠，避免依赖sysjobactivity的记录延迟或自身记录干扰：

  declare @LockResult int
  exec @LockResult = sp_getapplock 
      @Resource = 'updateDeliveryReceipts_Lock',
      @LockMode = 'Exclusive',
      @LockTimeout = 0 -- 立即返回，不等待
  
  if @LockResult >= 0 -- 获取锁成功
  begin
      exec @return_value = [dbo].[updateDeliveryReceipts]
          @messagesUpdated = @messagesUpdated OUTPUT,
          @dlrsDeleted = @dlrsDeleted OUTPUT
  
      exec sp_releaseapplock @Resource = 'updateDeliveryReceipts_Lock'
  end
  

内容的提问来源于stack exchange，提问作者Scott