SQL Server 2008 (SSMS): encontre a próxima data de execução de um trabalho

2

Gostaria de saber se há uma maneira de obter a próxima vez que um trabalho deve estar em execução no SQL Server 2008, usando uma consulta T-SQL ou mesmo no SSMS, se possível, sem precisar consultar todos os agendamentos. para todos os trabalhos.

Obrigado

    
por MaxiWheat 01.09.2010 / 19:56

5 respostas

6

Execute sp_help_job no banco de dados msdb. As colunas next_run_date e next_run_time possuem os valores que você está procurando.

    
por 01.09.2010 / 20:23
1

Porque eu amo alguns powershell:

$server = new-object microsoft.sqlserver.management.smo.server 'yourinstance';
$agent = $server.jobserver;
$job = $agent.jobs['your job name here'];
$job.nextrundate;
    
por 22.07.2013 / 16:22
0

Isso gerará um conjunto de resultados de linha única com o nome do seu trabalho e a próxima data / hora de execução.

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

É claro que você pode se livrar do DECLARE e do SET e apenas incluí-lo na junção da consulta interna.

    
por 01.09.2010 / 20:27
0

Você pode usar este procedimento para obter uma lista de todos os trabalhos, incluindo o último tempo de execução e a duração média:

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
    
por 22.07.2013 / 13:18
0

FYI para googlers que executam esse thread antigo, o Job Activity Monitor retorna muitas das mesmas informações que sp_help_jobs.

    
por 26.04.2017 / 15:07