A scheduled job has recorded scheduling metadata as well as its actual execution history. I check both before promising a next run.
A reader asked for the next run time, and a friend supplied the starting script. The original string comparison converted times to a partial 12-hour form without AM or PM. It also lost a separator in the saved next_run_date FROM text.
SELECT j.name AS job_name, s.name AS schedule_name,
j.enabled AS job_enabled, s.enabled AS schedule_enabled,
js.next_run_date, js.next_run_time,
CASE WHEN js.next_run_date > 0 THEN
TRY_CONVERT(datetime2(0),
STUFF(STUFF(RIGHT('00000000' + CONVERT(varchar(8), js.next_run_date),8),5,0,'-'),8,0,'-')
+ 'T' + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), js.next_run_time),6),3,0,':'),6,0,':'))
END AS next_run_datetime
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS s ON s.schedule_id = js.schedule_id
ORDER BY next_run_datetime, job_name, schedule_name;This SQL Server 2012-and-later query forms a typed date and time without the undocumented AGENT_DATETIME helper. Zero or unparseable values return NULL. A job with more than one schedule can produce more than one row.
Read scheduling state in context
sysjobschedules refreshes every 20 minutes. Its value is recorded scheduling metadata, not a real-time promise that a job will run at that instant. Agent availability, enabled state, and the actual schedule all matter.
The time uses the scheduling server’s local clock. Don’t label it UTC or compare two servers’ values without their timezone context. Metadata access also depends on the Agent permissions granted to the reader.
Reference: Agent job schedule metadata.
Related reading
- SQL SERVER – SQL Server Agent Missing in SQL Server Management Studio (SSMS)
- SQL SERVER – How to Get SQL Server Agent Properties?
- How to Find Service Account for SQL Server and SQL Server Agent? – Interview Question of the Week #179
- How to List All the SQL Server Jobs When Agent is Disabled? – Interview Question of the Week #171
- SQL SERVER – FIX: SQLServerAgent is not currently running so it can’t be notified of this action. (Microsoft SQL Server, Error: 22022)
- SQL SERVER – Execution Failed. See the Maintenance Plan and SQL Server Agent Job History Logs for Details
- Comprehensive Database Performance Health Check
A scheduled timestamp is not a completed execution, it is metadata that depends on the job and Agent state.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
40 Comments. Leave new
Hi! How can I see if the a previous execution of an Agent Job was manual or scheduled?
i m running a sql backup job for 25 databases which will take near about 8 hrs to completing while running this task can i change a store procedure that is being used in this job. Actually i want to exclude some Databases for next run and i m in a hurry to leave.
Use the approach shown at
What if instead of next running time, I want to get the last date and time my job ran? Does someone have any idea?
I am sure all the scripts posted here should at least run problem free at poster’s machine, unfortunately once they were posted here, some characters were automatically altered, which caused the scripts can’t be used as a copy and paste. What a pity.
In the CTE definition, the expression
RIGHT(‘0’+CAST(next_run_time AS VARCHAR(6)),6)
should be
RIGHT(‘000000’+CAST(next_run_time AS VARCHAR(6)),6)
as next_run_time, for as early times as 00:00:30AM, is represented as 30 and will transform as 030 with the former, and 000030 with later.
The only problem with this query is that the next_run_time value could be not accurate for jobs with an interval less then 20min because the sysjobschedules view is refreshed at the same interval, 20min. So the view (and the query from the article) will return a next_run_time that is actually in the past until the next time it will be refreshed. The only way to workaround this problem is this:
SELECT *
FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’,
‘set fmtonly off exec msdb.dbo.sp_get_composite_job_info’)
Form execution of that system stored procedure the next_run_time will be always the correct one. This procedure in turn gets that info from an extended stored procedure called xp_sqlagent_enum_jobs, so you can`t see that code. This is the reason why the only workaround is to use OPENROWSET.
try this: EXEC msdb.dbo.sp_help_job @Job_name = ‘Your Job Name’
The formatting of the next run time for times before 1 AM isn’t quite right. While 1 AM shows 010000, midnight will show 00 and 12:30 AM will show 03000.
Thanks a lot.
I’ve looked for this for two days.
even though I am able to see the next run and last run date time values in job activity monitor but when I am using above query its returning 0
use this to convert “jobtime” to normal datetime:
select msdb.dbo.agent_datetime(@dateInt,@timeInt)
Hi,
I have a job set up to run two times a day. However, the job is taking 11 hours time to complete. Will this effect the number of times the job will run although it is set to run two times a day?
Thanks in advance for the help.
Hi, is it possible to run jobs not in the same day? for example Monday 10PM – Tusday 2AM
Hi Pinal,
Good work, please keep it up.
One quick question (actually need help):
How can I take Inputs from user in SQL Server Job – I want to create a job to refresh a dev database so the job should ask three inputs Destination Database and Destination Server and Target Database. I mean I dont want to create separate jobs for each database. I want to create one generic job.
An early response would be appreciated.
Thanks in advance.
Shoaib
This will not give correct results if the job is running currently. This will only give correct results if the job is not running at the moment.