SQL SERVER – Find Next Running Time of Scheduled Job Using T-SQL

I often receive good question on blog, however, I do not always receive good answer for the questions. Imran Mohammed is one SQL Expert who spend his time helping SQL Community on this blog. I always read his answers as there is something to learn every time. Recently someone asked on blog about Finding next run time for Schedule Job using T-SQL. Imran came up with nice script. I have modified it a bit to adjust needs.

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_date
FROM 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)

Reference : Pinal Dave (http://blog.SQLAuthority.com), Imran Mohammed

About these ads

35 thoughts on “SQL SERVER – Find Next Running Time of Scheduled Job Using T-SQL

  1. Hello Pinal,

    Thanks for publishing this comment.

    I remember the question was something like this,

    I wanted to know the jobs that are scheduled for today and not yet executed.

    So I wrote this script in a way that it will check for todays job and also it will check for time, say its 4 PM now… it will list all the jobs that are scheduled to run after 4 PM… ( today ), it will not list those jobs before 4 PM ( in our example)

    I wrote this script in hurry as I was leaving to home from my job… so I did not check it completely… Thanks for fixing it.

    Thanks
    IM.

  2. When I ran it, I received this error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near ‘‘’.

  3. 1. Replace all the single quotes after copying and pasting the code. Mine always replace the single quotes with another character for some reason.

    2. Replace the CTE in the from clause with an embedded subselect:
    FROM sysjobs A ,(SELECT schedule_id, job_id, RIGHT(‘0′+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
    FROM sysjobschedules) CTE

    3. Comment out lines 2 and 3

    4. Run the script.

  4. Cleaned it up a bit, it’s useful to have for knowing what the upcoming jobs are without worrying about all the details (frequency, enabled, etc…) where they are many scripts available as well

    ————–
    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_date
    FROM sysjobschedules)
    SELECT A.name Job_Name
    –, next_run_date, next_run_time
    ,’Will be running today at ‘+
    Stuff(Stuff(right(’000000′+Cast(next_run_time as Varchar),6),3,0,’:’),6,0,’:’) ‘ScheduledAt’
    FROM sysjobs A ,CTE B
    WHERE A.job_id = B.job_id
    AND cast(cast(next_run_date as varchar(15)) as datetime) = CONVERT(VARCHAR(10),GETDATE(),101) – same date
    AND next_run_time > REPLACE(SUBSTRING(CONVERT( VARCHAR(30) , GETDATE(),120),12,10),’:’,”) – compare time
    ORDER BY ScheduledAt ASC

  5. Hi all!

    My name is James and I’m new here :). So far this is an incredible place for information and I have spent a ton of time reading and browsing around. Look forward to hearing from you!

  6. Is there a way to know that the scheduled jobs are NOT running on a specified time schedule? I encounter this one once we restarted the server and the jobs schedule missed.

  7. Is there any way that I can update the next run time with a script ?

    I have applied,

    Update sysjobschedules
    set next_run_date = CONVERT( VARCHAR(10) , GETDATE()+1,112)
    where job_id=’9F505A75-8A76-48F3-A4E1-12336DA0DF64′

    and it says success but doesnt reflect the results on job activity monitor.

  8. Hi

    1. I want a report when job start execute and finish then I Can calculate how much time taken to execute a particular job using TSQL

    2. Retrive all DTS or SSIS package schdule on server

    Thanks in advance

    Regards
    Jayant Dass

  9. Hi,

    I have scheduled a sql 2005 job to run at 1am in the morning,
    example:

    10-09-10 > I created a job

    So the job properties show scheduled for the first run on 11-09-10 , 1 am.

    Now, when I checked the job history on 11-09-10 I didnt see any job history for it.

    Howver I could see that the last executed date was 11-09-10, but no job history.

    Please advice.

  10. Just remember that sysjobschedules refreshes every 20 minutes (http://msdn.microsoft.com/en-us/library/ms188924.aspx), so may not reflect recent job runs. So what appears as a bug with no refresh of next run datetime is just a SQLAgent thing which magically disappears (probably after you’ve tried a few things so you put it down to your own genius).
    I’ve looked hard but can’t find how to force it to refresh or change the 20 minutes to something more acceptable, even with 2k8R2.

  11. Well, it seems master.dbo.xp_sqlagent_enum_jobs is the answer.
    This undocumented sproc sees the SQL Agent updates immediately.
    I’ve written a sproc to return the next run date & time as a datetime given the name of the job. I hope this helps someone out there, I know I could have done with it a few months ago and it’s good to give something back.

    Regards

    Derek

    /****** Object: UserDefinedFunction [dbo].[udfGetDateTimeFromInteger] Script Date: 08/16/2011 14:38:17 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    /* =============================================
    – Author: Derek Robinson
    – Create date: 25/05/11
    – Description: return a datetime based on 2 integers passed
    (used with scheduled jobs)

    – Usage: select dbo.udfGetDateTimeFromInteger(20110601, 0)
    – Returns: equivalent datetime

    – =============================================*/
    CREATE FUNCTION [dbo].[udfGetDateTimeFromInteger]
    (
    @intDate int,
    @intTime int
    )
    RETURNS datetime
    AS
    BEGIN
    – Declare the return variable here
    DECLARE @DT_datetime datetime = NULL,
    @str_date varchar(11),
    @str_time varchar(8)
    if(@intDate is not null and @intDate > 0)
    begin
    select @str_date = CONVERT(varchar(11),@intDate)
    select @str_date = SUBSTRING(@str_date,1,4)+’/’+SUBSTRING(@str_date,5,2)+’/’+SUBSTRING(@str_date,7,2)
    if @intTime=0
    select @str_time =’000000′
    else
    select @str_time = right(’0′+CONVERT(varchar(11),@intTime),6)
    select @str_time = SUBSTRING(@str_time,1,2)+’:’+SUBSTRING(@str_time,3,2)+’:’+SUBSTRING(@str_time,5,2)

    select @DT_datetime = CAST(@str_date+’ ‘+@str_time as datetime)
    end
    – Return the result of the function
    RETURN @DT_datetime

    END

    GO

    /* =============================================
    – Author: Derek Robinson
    – Create date: 16/08/11
    – Description: return a datetime on the next run date/time of a give @job_name
    uses undocumented proc xp_sqlagent_enum_jobs. Circumvents the 20 min delay in
    refreshing sysjobschedules. For those of us where 20 minutes is a lifetime.

    – Example usage: (highlight and run)
    declare @nextRunDateTime datetime
    exec uspGetNextRunDateTime @job_name=’ValueLink Price File Output’, @nextRunDateTime = @nextRunDateTime output
    select @nextRunDateTime

    – Returns: datetime of next run or NULL if not found

    – =============================================*/
    alter proc [dbo].[uspGetNextRunDateTime]
    (
    @job_name varchar(250),
    @nextRunDateTime datetime output
    )
    as
    BEGIN

    create table #xp_results(
    job_id uniqueidentifier not null,
    last_run_date int not null,
    last_run_time int not null,
    next_run_date int not null,
    next_run_time int not null,
    next_run_schedule_id int not null,
    requested_to_run int not null, — bool
    request_source int not null,
    request_source_id sysname collate database_default null,
    running int not null, — bool
    current_step int not null,
    current_retry_attempt int not null,
    job_state int not null )

    insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ”

    select @nextRunDateTime = dbo.udfGetDateTimeFromInteger(r.next_run_date, r.next_run_time)
    from #xp_results r inner join msdb..sysjobs j on
    r.job_id = j.job_id
    where j.name = @job_name

    drop table #xp_results

    RETURN

    END

  12. Hi Pinal,

    This is my first quaery on your blog..
    My reuirement is I wanted to shedule my job to run on particular date
    of every year and the dates are
    01/31
    04/30
    07/31
    10/31

    Please suggest me solution for this.

  13. Hi, I am writing to try and get resolution to a problem I am having. I have a new install of SQL 2008R2 and I have set up maintenance plans to backup the databases. I have scheduled the jobs to run at 10 pm. The first evening the jobs ran fine, then they stopped running. The new server is on a different domain, so I changed the jobs to use a sql login from my AD login. I can run the jobs manually at any time. I can even get the jobs to run if I set the schedule to run at 8 AM, but the jobs will not run on the 10pm schedule. What am I missing? Thanks in advance for any advice.

  14. Hi Pinal,

    I am SQL DBA and we are joined in linked in also,
    I have seen the above query but i think we can find the next run schedule job with my below query:

    select j.name,s.next_run_date,s.next_run_time from sysjobs j
    inner join sysjobschedules s on j.job_id = s.job_id
    where j.enabled =1
    and s.next_run_date = CONVERT(VARCHAR(8), GETDATE(), 112)

    Need your comments.

    Thanks
    MOHIT

    • As I mentioned above. sysjobschedules is only updated with the next run date time around 20 minutes after execution. If this is not an issue then fine, if it is use xp_sqlagent_enum_jobs which sees the change immediately.

  15. SELECT
    J.NAME JOB,

    DATEADD(SS,(H.RUN_TIME)%100,DATEADD(N,(H.RUN_TIME/100)%100,DATEADD(HH,H.RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),H.RUN_DATE),112))))
    JOB_STARTED,

    DATEADD(SS,((H.RUN_DURATION)%10000)%100,DATEADD(N,((H.RUN_DURATION)%10000)/100,DATEADD(HH,(H.RUN_DURATION)/10000,DATEADD(SS,(H.RUN_TIME)%100,DATEADD(N,(H.RUN_TIME/100)%100,DATEADD(HH,H.RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),H.RUN_DATE),112)))))))
    JOB_COMPLETED,

    CONVERT(VARCHAR(2),(H.RUN_DURATION)/10000) + ‘:’ +
    CONVERT(VARCHAR(2),((H.RUN_DURATION)%10000)/100)+ ‘:’ +
    CONVERT(VARCHAR(2),((H.RUN_DURATION)%10000)%100) RUN_DURATION,

    CASE H.RUN_STATUS
    WHEN 0 THEN ‘FAILED’
    WHEN 1 THEN ‘SUCCEEDED’
    WHEN 2 THEN ‘RETRY’
    WHEN 3 THEN ‘CANCELED’
    WHEN 4 THEN ‘IN PROGRESS’
    END RUN_STATUS
    ,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 SQL SERVER’
    ELSE ‘N/A’ END [FREQ]
    ,CASE
    WHEN S.NEXT_RUN_DATE > 0 THEN DATEADD(N,(NEXT_RUN_TIME%10000)/100,DATEADD(HH,NEXT_RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),NEXT_RUN_DATE),112)))
    ELSE CONVERT(DATETIME,CONVERT(VARCHAR(8),’19000101′),112) END NEXT_RUN
    ,S.NEXT_RUN_DATE
    ,S.NEXT_RUN_TIME
    FROM
    MSDB..SYSJOBHISTORY H,MSDB..SYSJOBS J, MSDB..SYSJOBSCHEDULES S,
    (SELECT MAX(INSTANCE_ID) INSTANCE_ID, JOB_ID FROM MSDB..SYSJOBHISTORY GROUP BY JOB_ID) M
    WHERE
    H.JOB_ID = J.JOB_ID AND J.JOB_ID = S.JOB_ID AND H.JOB_ID = M.JOB_ID AND H.INSTANCE_ID = M.INSTANCE_ID
    – AND
    – RUN_DATE = (YEAR(GETDATE())*10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())
    ORDER BY NEXT_RUN

    But NEXT_RUN_DATE/Time in some cases comes 0.

  16. 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.

  17. 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.

  18. 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.

  19. 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.

  20. 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.

  21. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s