Назад | Перейти на главную страницу

SQL Server 2008 (SSMS): поиск даты следующего запуска задания

Я хотел бы знать, есть ли способ получить в следующий раз, когда задание будет выполняться в SQL Server 2008, используя запрос T-SQL или даже в SSMS, если это возможно, без необходимости просматривать все расписания для всех рабочие места.

Спасибо

Запустите процедуру sp_help_job в базе данных msdb. Столбцы next_run_date и next_run_time содержат значения, которые вы ищете.

Потому что я люблю немного Powershell:

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

Это даст вам набор результатов в одну строку с вашим именем задания и датой / временем следующего запуска.

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

Конечно, вы можете избавиться от DECLARE и SET и просто включить их в соединение внутреннего запроса.

Вы можете использовать эту процедуру, чтобы получить список для всех заданий, включая 3 последних времени выполнения и среднюю продолжительность:

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

К вашему сведению, для гуглеров, которые работают в этом старом потоке, монитор активности заданий возвращает много той же информации, что и sp_help_jobs.