2 votes

MaxiWheat avatar

SQL Server 2008 (SSMS) : Trouver la date de la prochaine exécution d'un travail

Demandé el 1 de Septembre, 2010
Quand la question a-t-elle été
5040 affichage
Nombre de visites la question a
5 Réponses
Nombre de réponses aux questions
Résolu
Situation réelle de la question

Je voudrais savoir s'il existe un moyen d'obtenir la prochaine heure à laquelle un travail est censé s'exécuter dans SQL Server 2008, à l'aide d'une requête T-SQL ou même dans SSMS si possible, sans avoir à consulter toutes les planifications de tous les travaux.

Gracias

Demandé el 1 de Septembre, 2010 par MaxiWheat

Réponses

Trop de publicités?

6voto

JohnMcG avatar
JohnMcG Points 5062

Exécutez sp_help_job dans la base de données msdb. Les colonnes next_run_date et next_run_time ont les valeurs que vous recherchez.

Répondu el 1 de Septembre, 2010 par JohnMcG (5062 Points )

1voto

Ben Thul avatar
Ben Thul Points 2939

Parce que j'adore le powershell :

$server = new-object microsoft.sqlserver.management.smo.server 'yourinstance';
$agent = $server.jobserver;
$job = $agent.jobs['your job name here'];
$job.nextrundate;
Répondu el 22 de Juillet, 2013 par Ben Thul (2939 Points )

0voto

Warren Blanchet avatar
Warren Blanchet Points 881

Vous obtiendrez un ensemble de résultats à une seule ligne avec le nom de votre travail et la date/heure de la prochaine exécution.

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

Vous pouvez bien sûr vous débarrasser des DECLARE et SET et les inclure simplement dans la jointure de la requête interne.

Répondu el 1 de Septembre, 2010 par Warren Blanchet (881 Points )

0voto

Ohad Pick avatar
Ohad Pick Points 1

Vous pouvez utiliser cette procédure pour obtenir une liste de tous les travaux, y compris les trois derniers temps d'exécution et la durée moyenne :

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
Répondu el 22 de Juillet, 2013 par Ohad Pick (1 Points )

0voto

Djd080 avatar
Djd080 Points 11

Pour information, pour les googleurs qui tomberaient sur ce vieux fil de discussion, le Job Activity Monitor renvoie beaucoup des mêmes informations que sp_help_jobs.

Répondu el 26 de Avril, 2017 par Djd080 (11 Points )

Questions en vedette

Top Tags

Dans notre réseau

SistemesEz.com

SystemesEZ est une communauté de sysadmins où vous pouvez résoudre vos problèmes et vos doutes. Vous pouvez consulter les questions des autres sysadmins, poser vos propres questions ou résoudre celles des autres.

Powered by:

Yandex
X