為什麼作業的 next_run_time 是錯誤的？

我有一個關於工作統計的問題。所以我有一份工作時間表如下：每天在上午 12:00:00 到晚上 11:59:59 之間每 5 分鐘發生一次。時間表將從 2017 年 12 月 13 日開始使用。

在 2019-10-20 上午 11:27 我執行了兩個腳本來獲取 last_run_time 和 next_run_time，以下是腳本及其對應的結果：

USE msdb
GO
SELECT J.Name                     AS 'Job Name'
        ,'Job Enabled' =        
         CASE J.Enabled
                WHEN 1 THEN 'Yes'
                WHEN 0 THEN 'No'
         END
        ,STUFF(
         STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
         , 8, 0, '-')                   AS 'Job Schedule Start Date'
        ,STUFF(
         STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
         , 8, 0, '-')                   AS 'Job Schedule End Date'
        ,'Job Frequency' =
         CASE S.freq_type
                WHEN 1 THEN 'Once'
                WHEN 4 THEN 'Daily'
                WHEN 8 THEN 'Weekly'
                WHEN 16 THEN 'Monthly'
                WHEN 32 THEN 'Monthly relative'
                WHEN 64 THEN 'When SQLServer Agent starts'
         END
        ,'Last Run Date' = CONVERT(DATETIME, RTRIM(LASTRUN.run_date) + ' '
         + STUFF(STUFF(REPLACE(STR(RTRIM(LASTRUN.run_time), 6, 0),
         ' ', '0'), 3, 0, ':'), 6, 0, ':'))
        , 'Last Run Status' =
         CASE
                WHEN LASTRUN.run_status = 0 THEN 'Failed'
                WHEN LASTRUN.run_status = 1 THEN 'Succeeded'
                WHEN LASTRUN.run_status = 2 THEN 'Retry'
                WHEN LASTRUN.run_status = 3 THEN 'Cancelled'
                ELSE 'Unknown'
         END                           
        ,'Last Run Message' = LASTRUN.message
FROM dbo.sysjobs J
LEFT OUTER JOIN dbo.sysjobschedules JS
      ON J.job_id = JS.job_id
LEFT OUTER JOIN dbo.sysschedules S
      ON JS.schedule_id = S.schedule_id
LEFT OUTER JOIN (SELECT
             job_id
         ,MAX(run_duration) AS RUN_DURATION
      FROM dbo.sysjobhistory
      GROUP BY job_id) MAXDUR
      ON J.job_id = MAXDUR.job_id
LEFT OUTER JOIN (SELECT
             J1.job_id
         ,J1.RUN_DURATION
         ,J1.run_date
         ,J1.run_time
         ,J1.message
         ,J1.run_status
      FROM dbo.sysjobhistory J1
      WHERE instance_id = (SELECT
                    MAX(instance_id)
             FROM dbo.sysjobhistory J2
             WHERE J2.job_id = J1.job_id)) LASTRUN
      ON J.job_id = LASTRUN.job_id
ORDER BY 'Job Name'

use msdb
go
Select [job name] = jb.name 
   , [schedule name] = sc.name
   , [next run date] = js.next_run_date
   , [next run time] = js.next_run_time
From dbo.sysjobs as jb
Inner Join dbo.sysjobschedules as js on js.job_id = jb.job_id
Inner Join dbo.sysschedules as sc on js.schedule_id = sc.schedule_id

因此，根據圖片 next_run_date 應該是上午 11:30 而不是上午 11:15。你有沒有遇到過這樣的現象？你能幫我理解這個嗎？

sysjobschedules 上的文件顯示，裡面的數據每 20 分鐘刷新一次。

注意： sysjobschedules 表每 20 分鐘刷新一次，這可能會影響 sp_help_jobschedule 儲存過程返回的值。

這就是為什麼您的next_run_date&next_run_time列可能會顯示過時的數據。

要獲得更長的答案和深入了解。查看Aaron Bertrand的這個答案

他談到使用dbo.sysjobactivity.

您可以使用next_scheduled_run_date，這是來自此 dmv 的日期時間列。

範例查詢：

SELECT sj.Name, 
next_scheduled_run_date
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
   SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity); 

使用 是max(session)因為代理每次重新啟動時都會啟動一個新會話。

這導致

Name                            next_scheduled_run_date
LSAlert_My_machine              2019-10-20 11:34:00.000
LSRestore_My_machine_Test42     2019-10-20 11:45:00.000
LSCopy_My_machine_Test42        2019-10-20 11:45:00.000
syspolicy_purge_history         2019-10-21 02:00:00.000

---

SSMS

在呼叫作業活動監視器時跟踪 SSMS 正在做什麼以獲取作業資訊。xp_sqlagent_enum_jobslast_run_date 和 last_run_time以游標方式取自過程。

您可以使用此過程獲取工作資訊並將游標放在它上面。

獲取一份工作資訊的範例：

DECLARE @job_id uniqueidentifier
DECLARE @job_owner varchar(255)
SELECT @job_owner = SUSER_SNAME()

SELECT @job_id=job_id FROM msdb..sysjobs 
WHERE name = 'LSAlert_My_machine'
   EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id;

程序返回的第 2 列到第 5 列：

Last Run Date   Last Run Time   Next Run Date   Next Run Time
20191020        114400          20191020        114600

如果您希望為所有作業執行該過程：

DECLARE @job_id uniqueidentifier, @job_name varchar(255)
DECLARE @job_owner varchar(255)
SELECT @job_owner = SUSER_SNAME()
DECLARE @xp_results TABLE(Job_name varchar(255),Job_Id varbinary(16),last_run_date varchar(10),last_run_time varchar(10),next_run_date varchar(10), next_run_time varchar(10), next_run_schedule_id int, requested_to_run bit,request_source int, request_source_id int, running bit, current_step int, current_retry_attempt int, [State] int)

DECLARE C CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT [name],job_id FROM msdb..sysjobs 
OPEN C
FETCH NEXT FROM C INTO @job_name,@job_id
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @xp_results(Job_Id,last_run_date ,last_run_time ,next_run_date , next_run_time , next_run_schedule_id , requested_to_run ,request_source , request_source_id , running , current_step , current_retry_attempt , [State] )
   EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id;

UPDATE @xp_results
SET Job_name = @job_name
WHERE Job_Id = @job_id;

FETCH NEXT FROM C INTO @job_name,@job_id
END
CLOSE C
DEALLOCATE C
SELECT Job_name,last_run_date,last_run_time,next_run_date,next_run_time 
FROM @xp_results;

範例輸出

Job_name                        last_run_date   last_run_time   next_run_date   next_run_time
LSAlert_My_machine              20191020        115800          20191020        120000
LSCopy_My_machine_Test42        20191020        114500          20191020        120000
LSRestore_My_machine_Test42     20191020        114500          20191020        120000

引用自：https://dba.stackexchange.com/questions/251527