Why the job's next_run_time is wrong?

I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017.

in 11:27 AM 2019-10-20 I ran two scripts to get last_run_time and next_run_time, below are the scripts and their corresponding results:

```
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(r

The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes.


NOTE: The sysjobschedules table refreshes every 20 minutes, which may
affect the values returned by the sp_help_jobschedule stored
procedure.

Which is why your next_run_date & next_run_time columns could show out of date data.

For a much longer answer & deep dive. Check out this answer by Aaron Bertrand

He talks about using dbo.sysjobactivity.

You could use the next_scheduled_run_date, which is a datetime column from this dmv.

Example query:

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);

With the max(session) used because the agent starts a new session each time it restarts.

This results in

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

When tracing what SSMS is doing to get the job info when calling job activity monitor. the last_run_date & last_run_time are taken from the xp_sqlagent_enum_jobs procedure, in a cursor wise manner.

You could get the job info & place a cursor over it by using this procedure.

An example of getting the info for one job:

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;

2nd until 5th columns returned by the procedure:

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

If you wish to run the procedure for all jobs:

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;

Example output

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

StackExchange Database Administrators Q#251527, answer score: 6