sysjobschedules contains 2 columns
next_run_time. With the help of
sysschedules and column
enable it should be easy to determine the next running date/time of a specific job.
However, when you change a schedule,
sysjobschedules is only refreshed 20 minutes after alteration by the Sql Server Agent.
sysjobactivity contains a datetime
next_schedule_run_date which is updated in real time.
But I’ve failed to see how to link
sysschedules since it doesn’t contain a column to identify the id of the schedule.
Can someone help?
You can use the (documented) stored procedure
sp_help_job to get the next execution time, however progratically capturing the results can be difficult due to the limitation around nested
INSERT...EXEC calls. As an alternative, many folks use the (undocumented) stored procedure
xp_sqlagent_enum_jobs which captures much of the same info.
Like you, I prefer to capture the data from the base tables directly using an alternate approach that I wrote about in a blog post, and have published a T-SQL function on GitHub as part of my DBA Database.
The table-valued function (TVF) definition itself is just a single SELECT statement that takes a job name, and returns a bunch of stuff, including last & next run time, and whether the job is currently running:
SELECT TOP 1 IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END, LastRunTime = ja.start_execution_date, NextRunTime = ja.next_scheduled_run_date, LastJobStep = js.step_name, JobOutcome = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running' WHEN run_status = 0 THEN 'Failed' WHEN run_status = 1 THEN 'Succeeded' WHEN run_status = 2 THEN 'Retry' WHEN run_status = 3 THEN 'Cancelled' END FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id AND ja.run_requested_date IS NOT NULL AND ja.start_execution_date IS NOT NULL LEFT JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id AND js.step_id = ja.last_executed_step_id LEFT JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id WHERE j.name = @JobName ORDER BY ja.start_execution_date DESC;
Because it’s a TVF, you can pass a value directly to it and get a result set back:
SELECT * FROM dbo.SqlAgentJob_GetStatus('Test Job');
Or APPLY it across a table for a set of results (in this case I’m getting all job in job_category 10):
SELECT sts.* FROM msdb.dbo.sysjobs j CROSS APPLY dbo.SqlAgentJob_GetStatus(j.name) sts WHERE j.category_id = 10;
Or get a list of all SQL Agent jobs that are currently running:
SELECT sts.* FROM msdb.dbo.sysjobs j CROSS APPLY dbo.SqlAgentJob_GetStatus(j.name) sts WHERE sts.IsRunning = 1;
You’ll notice that my code doesn’t use
sysschedules, because it just isn’t necessary.
If you wanted to make use of
sysschedules in code, you would do something like this, joining from
sysschedules. Keep in mind that jobs can have zero to many schedules.
SELECT JobName = j.name, ScheduleName = s.name, ...other columns... FROM msdb.dbo.sysjobs AS j LEFT JOIN msdb.dbo.sysjobschedules AS js ON j.job_id = js.job_id LEFT JOIN msdb.dbo.sysschedules AS s ON js.schedule_id = s.schedule_id;
To determine the “next run date/time” you would need to essentially identify active schedules for the job, and take the next run date out of multiple schedules.
sysjobactivity does that heavy lifting for you by computing the next run date (you can think of it as the “actual next execution”)–but it simply doesn’t keep track of which schedule is responsible for that execution. If that matters to you, you’ll need to hunt that down yourself by looking at what schedules are active for that job.