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(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
So, according to the pictures next_run_date should be 11:30 AM instead of 11:15 AM. Have you ever faced with this kind of phenomenon? Could you please help me understand this?
Best Answer
The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes.
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:
With the
max(session)
used because the agent starts a new session each time it restarts.This results in
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:
2nd until 5th columns returned by the procedure:
If you wish to run the procedure for all jobs:
Example output