Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I would like to know if there is a way to get the next time a job is supposed to be running in SQL Server 2008, using a T-SQL query or even in SSMS if possible, without having to consult all the schedules for all the jobs.

Thank you

share|improve this question

4 Answers 4

up vote 5 down vote accepted

Run sp_help_job in the msdb database. The next_run_date and next_run_time columns have the values you are looking for.

share|improve this answer

This'll get you a single row result set with your job name and the next run date/time.

DECLARE @JobName sysname
SET @JobName='Query Tool Daily Routines'

SELECT
    JobName,
    MAX(NextRunTime) as NextRunTime
FROM (
    SELECT 
        j.name as JobName,
        cast(
            CONVERT(CHAR(8), next_run_date, 112) 
            + ' ' 
            + STUFF(STUFF(RIGHT('000000' 
            + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':')
            as datetime) as NextRunTime
    FROM msdb.dbo.sysjobs j
    join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
        and j.name=@JobName
) t1
group by JobName

You can of course get rid of the DECLARE and SET and just include it in the join of the inner query.

share|improve this answer

You can use this procedure, to get a list for all jobs including 3 last running time, and average duration:

CREATE PROCEDURE Job_Help
            @sResultTableName       varchar(128) = NULL,
            @sDailyTableName        varchar(128) = NULL,
            @lUniqueId              int = NULL,
            @bEnabled               bit = NULL

AS

/*******************  Variables  *********************************************************************/

DECLARE @iErrorCode                 int
DECLARE @sSql                       nvarchar(4000)

SET     @iErrorCode = @@ERROR

/*******************  Verify parameters  *********************************************************************/

IF @sResultTableName IS NULL RETURN
IF @sDailyTableName IS NULL RETURN
IF @lUniqueId IS NULL RETURN

/************************************************************************************************************/
-- initializes the real temp table name

SET @sResultTableName   = LTRIM (RTRIM (@sResultTableName)) + CONVERT (varchar(20), CONVERT (decimal(20,0), @lUniqueId))
SET @sDailyTableName    = LTRIM (RTRIM (@sDailyTableName)) + CONVERT (varchar(20), CONVERT (decimal(20,0), @lUniqueId))

/************************************************************************************************************/

IF @iErrorCode = 0
    BEGIN
        SET @sSql = 
            N'IF NOT EXISTS(    SELECT name ' + char(13) +
            '           FROM    tempdb..sysobjects ' + char(13) +
            '           WHERE   name = N''' + @sResultTableName + '''' + char(13) +
            '           AND     type = ''U'') ' + char(13) +
            '   CREATE TABLE ' + @sResultTableName + ' ( ' + char(13) +
            '           job_id                  uniqueidentifier    NOT NULL, ' + char(13) +
            '           sJobName                sysname             NOT NULL, ' + char(13) +
            '           bEnabled                bit                 NOT NULL, ' + char(13) +
            '           dtCreated               datetime            NULL, ' + char(13) +
            '           dtModified              datetime            NULL, ' + char(13) +
            '           dtNextRun               datetime            NULL, ' + char(13) +
            '           dtPreviousRunStart1     datetime            NULL, ' + char(13) +
            '           dtPreviousRunEnd1       datetime            NULL, ' + char(13) +
            '           sDuration1              varchar(10)         NULL, ' + char(13) +
            '           dtPreviousRunStart2     datetime            NULL, ' + char(13) +
            '           dtPreviousRunEnd2       datetime            NULL, ' + char(13) +
            '           sDuration2              varchar(10)         NULL, ' + char(13) +
            '           dtPreviousRunStart3     datetime            NULL, ' + char(13) +
            '           dtPreviousRunEnd3       datetime            NULL, ' + char(13) +
            '           sDuration3              varchar(10)         NULL, ' + char(13) +
            '           sAvgDuration            varchar(10)         NULL, ' + char(13) +
            '           iDuration1              int                 NULL, ' + char(13) +
            '           iDuration2              int                 NULL, ' + char(13) +
            '           iDuration3              int                 NULL, ' + char(13) +
            '           iTempAvgDuration        int                 NULL) ' + char(13) +
            'ELSE ' + char(13) +
            '   TRUNCATE TABLE ' + @sResultTableName

        EXEC sp_executesql @sSql

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0

/*********************************************************************************************************************/

IF @iErrorCode = 0
    BEGIN
        SET @sSql = 
            N'INSERT INTO ' + @sResultTableName + ' ( ' + char(13) +
            '           job_id, ' + char(13) +
            '           sJobName, ' + char(13) +
            '           bEnabled, ' + char(13) +
            '           dtCreated, ' + char(13) +
            '           dtModified, ' + char(13) +
            '           dtNextRun) ' + char(13) +
            '   SELECT  DISTINCT J.job_id, ' + char(13) +
            '           J.name, ' + char(13) +
            '           J.enabled, ' + char(13) +
            '           J.date_created, ' + char(13) +
            '           J.date_modified, ' + char(13) +
            '           CASE WHEN S.next_run_date = 0 THEN 0 ELSE convert (smalldatetime, substring (convert (varchar(10), S.next_run_date), 1, 4) + ''/'' + substring (convert (varchar(10), S.next_run_date), 5, 2) + ''/'' + substring (convert (varchar(10), S.next_run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), S.next_run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), S.next_run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), S.next_run_time), 6), 5, 2)) END ' + char(13) +
            '   FROM    msdb..sysjobs J INNER JOIN msdb..sysjobschedules S ' + char(13) +
            '   ON      J.job_id = S.job_id ' + char(13)

        IF NOT @bEnabled IS NULL
            SET @sSql = @sSql +
            '   WHERE   J.enabled = @bEnabled '

        EXEC sp_executesql  @sSql, N'@bEnabled bit', @bEnabled

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0

IF @iErrorCode = 0
    BEGIN
        -- dtPreviousRunStart1, dtPreviousRunEnd1, iDuration1
        SET @sSql = 
            N'UPDATE    J ' + char(13) +
            ' SET       dtPreviousRunStart1 = run_date, ' + char(13) +
            '           dtPreviousRunEnd1   = CONVERT (datetime, DATEADD (second, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2)), DATEADD (minute, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2)), DATEADD (hour, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2)), CONVERT (datetime, run_date))))), ' + char(13) +
            '           sDuration1          = CASE WHEN run_duration IS NULL THEN NULL ELSE substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2) END, ' + char(13) +
            '           iDuration1          = ISNULL (run_duration, 0)' + char(13) +
            ' FROM      ' + @sResultTableName + ' J INNER JOIN ( ' + char(13) +
            '           SELECT  A.job_id, ' + char(13) +
            '                   A.run_date, ' + char(13) +
            '                   A.run_duration ' + char(13) +
            '           FROM    ( ' + char(13) +
            '                   SELECT  job_id, ' + char(13) +
            '                           convert (smalldatetime, substring (convert (varchar(10), run_date), 1, 4) + ''/'' + substring (convert (varchar(10), run_date), 5, 2) + ''/'' + substring (convert (varchar(10), run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 5, 2))  run_date, ' + char(13) +
            '                           run_duration ' + char(13) +
            '                   FROM    msdb..sysjobhistory ' + char(13) +
            '                   WHERE   step_id = 0 ' + char(13) +
            '                   AND     run_status = 1 ' + char(13) +
            '           ) A     INNER JOIN ( ' + char(13) +
            '                   SELECT  job_id, ' + char(13) +
            '                           MAX (convert (smalldatetime, substring (convert (varchar(10), run_date), 1, 4) + ''/'' + substring (convert (varchar(10), run_date), 5, 2) + ''/'' + substring (convert (varchar(10), run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_time), 6), 5, 2)))    run_date ' + char(13) +
            '                   FROM    msdb..sysjobhistory ' + char(13) +
            '                   WHERE   step_id = 0 ' + char(13) +
            '                   AND     run_status = 1 ' + char(13) +
            '                   GROUP BY job_id ' + char(13) +
            '           ) B ' + char(13) +
            '           ON      A.job_id    = B.job_id ' + char(13) +
            '           AND     A.run_date  = B.run_date ' + char(13) +
            ' ) C ' + char(13) +
            ' ON        J.job_id = C.job_id '

        EXEC sp_executesql @sSql

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0

IF @iErrorCode = 0
    BEGIN
        -- dtPreviousRunStart2, dtPreviousRunEnd2, iDuration2
        SET @sSql = 
            N'UPDATE    J ' + char(13) +
            ' SET       dtPreviousRunStart2 = run_date, ' + char(13) +
            '           dtPreviousRunEnd2   = CONVERT (datetime, DATEADD (second, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2)), DATEADD (minute, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2)), DATEADD (hour, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2)), CONVERT (datetime, run_date))))), ' + char(13) +
            '           sDuration2          = CASE WHEN run_duration IS NULL THEN NULL ELSE substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2) END, ' + char(13) +
            '           iDuration2          = ISNULL (run_duration, 0)' + char(13) +
            ' FROM      ' + @sResultTableName + ' J INNER JOIN ( ' + char(13) +
            '           SELECT  A.job_id, ' + char(13) +
            '                   A.run_date, ' + char(13) +
            '                   A.run_duration ' + char(13) +
            '           FROM    ( ' + char(13) +
            '                   SELECT  H1.job_id, ' + char(13) +
            '                           convert (smalldatetime, substring (convert (varchar(10), H1.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H1.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H1.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 5, 2))    run_date, ' + char(13) +
            '                           H1.run_duration ' + char(13) +
            '                   FROM    msdb..sysjobhistory H1 ' + char(13) +
            '                   WHERE   H1.step_id = 0 ' + char(13) +
            '                   AND     H1.run_status = 1 ' + char(13) +
            '           ) A     INNER JOIN ( ' + char(13) +
            '                   SELECT  H2.job_id, ' + char(13) +
            '                           MAX (convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2)))  run_date ' + char(13) +
            '                   FROM    msdb..sysjobhistory H2 INNER JOIN ' + @sResultTableName + ' J2 ' + char(13) +
            '                   ON      H2.job_id = J2.job_id ' + char(13) +
            '                   WHERE   H2.step_id = 0 ' + char(13) +
            '                   AND     H2.run_status = 1 ' + char(13) +
            '                   AND     convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2)) < J2.dtPreviousRunStart1 ' + char(13) +
            '                   GROUP BY H2.job_id ' + char(13) +
            '           ) B ' + char(13) +
            '           ON      A.job_id    = B.job_id ' + char(13) +
            '           AND     A.run_date  = B.run_date ' + char(13) +
            ' ) C ' + char(13) +
            ' ON        J.job_id = C.job_id '

        EXEC sp_executesql @sSql

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0

IF @iErrorCode = 0
    BEGIN
        -- dtPreviousRunStart3, dtPreviousRunEnd3, iDuration3
        SET @sSql =
            N'UPDATE    J ' + char(13) +
            ' SET       dtPreviousRunStart3 = run_date, ' + char(13) +
            '           dtPreviousRunEnd3   = CONVERT (datetime, DATEADD (second, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2)), DATEADD (minute, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2)), DATEADD (hour, CONVERT (int, substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2)), CONVERT (datetime, run_date))))), ' + char(13) +
            '           sDuration3          = CASE WHEN run_duration IS NULL THEN NULL ELSE substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), run_duration), 6), 5, 2) END, ' + char(13) +
            '           iDuration3          = ISNULL (run_duration, 0)' + char(13) +
            ' FROM      ' + @sResultTableName + ' J INNER JOIN ( ' + char(13) +
            '           SELECT  A.job_id, ' + char(13) +
            '                   A.run_date, ' + char(13) +
            '                   A.run_duration ' + char(13) +
            '           FROM    ( ' + char(13) +
            '                   SELECT  H1.job_id, ' + char(13) +
            '                           convert (smalldatetime, substring (convert (varchar(10), H1.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H1.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H1.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H1.run_time), 6), 5, 2))    run_date, ' + char(13) +
            '                           H1.run_duration ' + char(13) +
            '                   FROM    msdb..sysjobhistory H1 ' + char(13) +
            '                   WHERE   H1.step_id = 0 ' + char(13) +
            '                   AND     H1.run_status = 1 ' + char(13) +
            '           ) A     INNER JOIN ( ' + char(13) +
            '                   SELECT  H2.job_id, ' + char(13) +
            '                           MAX (convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2)))  run_date ' + char(13) +
            '                   FROM    msdb..sysjobhistory H2 INNER JOIN ' + @sResultTableName + ' J2 ' + char(13) +
            '                   ON      H2.job_id = J2.job_id ' + char(13) +
            '                   WHERE   H2.step_id = 0 ' + char(13) +
            '                   AND     H2.run_status = 1 ' + char(13) +
            '                   AND     convert (smalldatetime, substring (convert (varchar(10), H2.run_date), 1, 4) + ''/'' + substring (convert (varchar(10), H2.run_date), 5, 2) + ''/'' + substring (convert (varchar(10), H2.run_date), 7, 2) + '' '' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 1, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 3, 2) + '':'' + substring (RIGHT (''000000'' + convert (varchar(10), H2.run_time), 6), 5, 2)) < J2.dtPreviousRunStart2 ' + char(13) +
            '                   GROUP BY H2.job_id ' + char(13) +
            '           ) B ' + char(13) +
            '           ON      A.job_id    = B.job_id ' + char(13) +
            '           AND     A.run_date  = B.run_date ' + char(13) +
            ' ) C ' + char(13) +
            ' ON        J.job_id = C.job_id '

            EXEC sp_executesql @sSql

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0

IF @iErrorCode = 0
    BEGIN
        -- updates iAvgDuration
        SET @sSql =
            N'UPDATE    ' + @sResultTableName + char(13) +
            ' SET       iTempAvgDuration = ( convert (int, substring (sDuration1, 1, 2) * 3600) + convert (int, substring (sDuration1, 4, 2) * 60) + convert (int, substring (sDuration1, 7, 2)) + ' + char(13) +
            '                                convert (int, substring (sDuration2, 1, 2) * 3600) + convert (int, substring (sDuration2, 4, 2) * 60) + convert (int, substring (sDuration2, 7, 2)) + ' + char(13) +
            '                                convert (int, substring (sDuration3, 1, 2) * 3600) + convert (int, substring (sDuration3, 4, 2) * 60) + convert (int, substring (sDuration3, 7, 2))) / 3 '

        EXEC sp_executesql @sSql

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0

IF @iErrorCode = 0
    BEGIN
        -- updates sAvgDuration
        SET @sSql =
            N'UPDATE    ' + @sResultTableName + char(13) +
            ' SET       sAvgDuration = RIGHT (''00'' + CONVERT (varchar(10), iTempAvgDuration / 3600), 2) + '':'' + ' + char(13) +
            '                          RIGHT (''00'' + CONVERT (varchar(10), (iTempAvgDuration - (iTempAvgDuration / 3600) * 3600) / 60), 2) + '':'' + ' + char(13) +
            '                          RIGHT (''00'' + CONVERT (varchar(10), (iTempAvgDuration - (iTempAvgDuration / 3600) * 3600) - ((iTempAvgDuration - (iTempAvgDuration / 3600) * 3600) / 60 * 60)), 2) ' 

        EXEC sp_executesql @sSql

        SET @iErrorCode = @@ERROR
    END     -- IF @iErrorCode = 0
share|improve this answer

Because I love me some powershell:

$server = new-object microsoft.sqlserver.management.smo.server 'yourinstance';
$agent = $server.jobserver;
$job = $agent.jobs['your job name here'];
$job.nextrundate;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.