Job management: How to get the next running date by querying MSDB

Question

Table sysjobschedules contains 2 columns next_run_date and 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.

Table sysjobactivity contains a datetime next_schedule_run_date which is updated in real time.

But I’ve failed to see how to link sysjobactivity to sysschedules since it doesn’t contain a column to identify the id of the schedule.

Can someone help?

asked 2020-11-27 by davidc2p

---

Answer

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;

What about sysschedules?

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 sysjobs to sysjobschedules to 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.

answered 2020-11-27 by Andy Mallon