I often receive a good question on the blog, however, I do not always receive a good answer for the questions. Recently someone asked on a blog about Finding next run time for Schedule Job using T-SQL. My friend came up with a nice script. I have modified it a bit to adjust needs. This blog post is about finding the next running time of scheduled job using T-SQL.
USE msdb
;WITH CTE AS (SELECT schedule_id, job_id,
RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS
next_run_time, next_run_dateFROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND (SUBSTRING( CONVERT(VARCHAR(10),
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >
SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)
Here is a few additional blog post on the related subject of scheduled jobs.
- 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 cannot 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
If you ever need help with SQL Server Performance, do not hesitate to reach out to me for Comprehensive Database Performance Health Check.
Reference: Pinal Dave (https://blog.sqlauthority.com),
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.