SQL Server - TSQL 检测因超限而错过的代理作业 [英] SQL Server - TSQL to Detect missed Agent jobs due to overrun

问题描述

我正在尝试使用 TSQL 来检测 JOB 是否已经超出并错过了下一个计划.

I am trying to work out the TSQL to detect if a JOB has overran and missed its next schedule.

场景:作业计划每小时运行一次(可能启用了多个计划)，但由于网络延迟等原因，作业停止了 10 分钟，现在运行了 90 分钟，因此错过了它每小时开始.

Scenario: Job is scheduled to run every hour (there may be more than one schedule enabled), but due to some reason such as network delays, the job stops taking 10 minutes and is now running for 90 minutes and therefore missing it's hourly start.

有一个名为 msdb.dbo.sysjobactivity 的表，其中有一个名为 next_scheduled_run_date 的列，但此日期仅在当前运行的作业完成后才会更新.这与作业活动监视器的行为相同，下一次运行日期仅在当前作业完成后更新.

There is a table called msdb.dbo.sysjobactivity, which has a column called next_scheduled_run_date but this date only gets update after the currently running job has finished. This is the same behaviour as the Job Activity monitor, the next run date only updates after the current job finishes.

msdb.dbo.sysjobactivity 根据以下详细信息更新:https://dba.stackexchange.com/questions/22644/how-and-when-does-sql-agent-update-next-run-date-next-运行时值

msdb.dbo.sysjobactivity is updated as per the details in: https://dba.stackexchange.com/questions/22644/how-and-when-does-sql-agent-update-next-run-date-next-run-time-values

问题:如何根据作业运行时的当前作业开始时间计算作业的下一次计划运行.分配的时间表可能不止一个.

Question: How can I calculate the next scheduled run for a job, based on the current job start time while the job is running. There may be more than a single schedule assigned.

感谢任何指点.

推荐答案

这是一个棘手的问题.首先，您包含的 stackexchange 链接非常好.我遵循了这一点，并进行了自己的跟踪.下面的代码将使您朝着正确的方向前进.您可能需要进行详细测试，我发现当答案是同一分钟但秒数稍微超出时，我遇到了一些计时问题.您当然可以根据需要修改它.

This is a tricky one. Firstly the stackexchange link that you included is excellent. I followed that as well as doing my own trace. The code below will set you in the right direction. You may need to do detailed testing, I found that I had some timing issues when the answer was to the same minute but the seconds were slightly out. You can of course modify this as needed.

这里是代码的初稿，它应该让你朝着正确的方向前进

here is the first draft of the code which should put you in the right direction

declare @jobname [sysname] = 'Check'
Select   
     nextOne = min(
        case freq_subday_type 
            when 8 then dateadd(hour  , (Datediff(hour  , [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time), getdate())/freq_subday_interval + 1) * freq_subday_interval, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time))
            when 4 then dateadd(minute, (Datediff(minute, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time), getdate())/freq_subday_interval + 1) * freq_subday_interval, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time))
            when 2 then dateadd(second, (Datediff(minute, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time), getdate())/freq_subday_interval + 1) * freq_subday_interval, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time))
        end)
FROM msdb.[dbo].[sysschedules] ss
inner join msdb.[dbo].[sysjobschedules] sjs on ss.schedule_id = sjs.schedule_id
inner join msdb.[dbo].[sysjobs] sj on sjs.job_id = sj.job_id
where sj.Name = @jobname  and ss.enabled = 1

这篇关于SQL Server - TSQL 检测因超限而错过的代理作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！