sql-server - 如何准确检测SQL Server作业是否正在运行并处理已经运行的作业？

Question

我目前正在使用这样的代码来检测 SQL Server 作业是否正在运行。 (这是 SQL Server 2005，所有 SP)

return (select isnull(  
(select top 1 CASE 
    WHEN current_execution_status = 4 THEN 0
    ELSE 1
    END
from openquery(devtestvm, 'EXEC msdb.dbo.sp_help_job')
where current_execution_status = 4 and
    name = 'WQCheckQueueJob' + cast(@Index as varchar(10))
), 1)
)

没有问题，一般来说，它工作得很好。

但是......(总是一个但是)

有时，我会调用它，返回“作业未运行”结果，此时我将尝试通过

启 Action 业

exec msdb.dbo.sp_start_job @JobName

SQL 将返回“SQLAgent 已拒绝启 Action 业，因为它已有待处理的请求”。

好的。也不是问题。可以想象，在该代码启动目标作业之前，但在检查它是否已启动之后，有一个轻微的窗口可以启动目标作业。但是，我可以将其包装在 try catch 中并忽略该错误，对吗？

begin try
if dbo.WQIsQueueJobActive(@index) = 0 begin
    exec msdb.dbo.sp_start_job @JobName
    break
end         
end try begin catch
    -- nothing here
end catch

问题就在这里。

十有八九，这个效果很好。 SQL 代理将引发错误，它被捕获，并且处理将继续，因为作业已经在运行，没有坏处，也没有犯规。

但有时，我会在“作业历史记录” View 中收到一条消息(请记住上面的代码，以检测特定作业是否正在运行，如果没有从另一个作业实际运行，则启动它)，说明该作业失败，因为“SQLAgent 拒绝启 Action 业，因为它已有待处理的请求”。

当然，这正是 TRY CATCH 应该处理的错误!

当这种情况发生时，正在执行的作业就会终止，但据我所知，并没有立即终止，只是非常接近。我把日志记录到处都是，但没有一致性。一次失败，它会在位置 a，下一次会在位置 b。在某些情况下，A 地和 B 地除了一个

select @var = 'message'

在他们之间。很奇怪。基本上，该作业似乎被毫不客气地转储，并且作业中剩下的任何要执行的内容都根本不被执行。

但是，如果我删除“exec StartJob”(或者当我知道目标作业尚未运行时仅调用一次)，则一切都会正常运行，并且作业中的所有处理都会运行。

这一切背后的目的是通过触发器(除其他外)启动一项作业，并且，如果该作业已经启动，则确实不需要“再次启动它”。

有人在 SQL 代理的作业处理中遇到过这样的行为吗？

编辑:当前的控制流程如下:

1. 更改表(更新或插入)...
2. 触发调用...的触发器
3. 调用...的存储过程
4. sp_Start_Job 其中...
5. 开始一项特定的工作...
6. 调用另一个存储过程(称为 CheckQueue)...
7. 执行一些处理并...
8. 检查多个表格，根据其内容可能...
9. 在另一个作业上调用 sp_start_job 以启动第二个同时作业处理额外的工作(第二个作业也调用 CheckQueue 存储过程)但这两个调用对完全独立的数据集进行操作)

Answer 1

首先，您有机会了解服务代理吗？从你的描述来看，这似乎就是你真正想要的。

区别在于，您不是启 Action 业，而是将数据放入 SB 队列，SB 会异步调用您的处理过程，并完全回避已经运行的作业等问题。它将自动生成/终止其他线程需求决定，它负责订单等。

这是一个很好的(并且模糊相关的)教程。 http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

让我们假设您出于任何原因都不能使用 SB(但说真的，可以!)。

使用作业 spid 的 context_info 怎么样？

1. 您的作业调用一个包装程序来单独执行每个步骤。

2. 包装过程中的第一个语句是

   DECLARE @context_info VARBINARY(30)
   SET @context_info = CAST('MyJob1' AS VARBINARY)
   SET CONTEXT_INFO @context_info
   

3. 当你的过程完成时(或在你的 catch block 中)

   SET CONTEXT_INFO 0x0
   

4. 当您考虑给您的工作打电话时，请执行以下操作:

   IF NOT EXISTS (SELECT * FROM master..sysprocesses WITH (NOLOCK) WHERE context_info=CAST('MyJob1' AS VARBINARY))
       EXEC StartJob
   

当你的包装程序终止或连接关闭时，你的 context_info 就会消失。

您还可以使用全局临时表(即 ##JobStatus)，当引用它的所有 spid 断开连接或显式删除它时，它们将消失。

一些想法。