Voir l’historique d’exécution des jobs par une requête

Publié le par

Si la visionneuse du journal est pratique pour voir l’historique des exécutions d’un job, comment peut-on voir s’il existe des jobs dont l’exécution a échoué (ou réussi) pour pouvoir l’encapsuler dans une application ou un rapport SSRS par exemple ?

Les tables système de la base de données MSDB nous permettent de spécifier une requête nous permettant de retrouver tous les informations dont nous avons besoin à propos des jobs :

– msdb.dbo.sysjobs stocke la liste de tous les jobs présents sur l’instance, qu’ils soient activés ou non
Рmsdb.dbo.sysjobhistory stocke la liste de toutes les ex̩cutions des jobs et de leurs ̩tapes
– msdb.dbo.sysjobsteps stocke la description de toutes les étapes de tous les jobs de l’instance

Voici donc, par exemple, comment retrouver tous les échecs pour le job nommé ‘TEST’, avec leur date :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
---------------------------------
-- Nicolas SOUQUET - 24/07/2009 -
---------------------------------
SELECT nomJob,
    CAST(STUFF(STUFF(runDateTime, 12, 0, ':'), 15, 0, ':') AS DATETIME) AS DateEchecExecJob
FROM
(
  SELECT J.name AS nomJob,
      CAST(H.run_date AS CHAR(8)) + ' ' +
      CASE LEN(CAST(H.run_time AS VARCHAR(6)))
        WHEN 5 THEN '0' + CAST(H.run_time AS CHAR(5))
        ELSE CAST(H.run_time AS CHAR(6))
      END AS runDateTime
  FROM msdb.dbo.sysjobs J
  JOIN msdb.dbo.sysjobhistory H
    ON J.job_id = H.job_id
  WHERE J.name = 'TEST'
  AND J.enabled = 1 -- Le job est activé
  AND H.run_status = 0 -- 0 : Echec | 1 : Réussite
  AND H.step_id = 0 -- quelle que soit l'étape du job
) AS JOB_FAILED_EXEC (nomJob, runDateTime)

Et si l’on veut être relativement complet :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
---------------------------------
-- Nicolas SOUQUET - 24/07/2009 -
---------------------------------
SELECT  nomJob
  , nomEtapeJob
  , numeroEtapeJob
  , msgEtape
  , CAST(STUFF(STUFF(dateExecutionEtape, 12, 0, ':'), 15, 0, ':') AS DATETIME) AS dateExecutionEtape
  , dureeExecutionEtape
  , statutExecutionEtape
FROM  
(  
  SELECT    J.name AS nomJob
      , S.step_name AS nomEtapeJob
      , S.step_id AS numeroEtapeJob
      , ISNULL('Erreur : ' + M.description, H.message) AS msgEtape
      , CAST(H.run_date AS CHAR(8)) + ' ' +  
      CASE LEN(CAST(H.run_time AS VARCHAR(6)))
        WHEN 1 THEN '00000' + CAST(H.run_time AS CHAR(1))  
        WHEN 2 THEN '0000' + CAST(H.run_time AS CHAR(2))  
        WHEN 3 THEN '000' + CAST(H.run_time AS CHAR(3))  
        WHEN 4 THEN '00' + CAST(H.run_time AS CHAR(4))  
        WHEN 5 THEN '0' + CAST(H.run_time AS CHAR(5))  
        ELSE CAST(H.run_time AS CHAR(6))  
      END AS dateExecutionEtape
      , CASE LEN(CAST(H.run_duration AS VARCHAR(10)))  
        WHEN 1 THEN '00:00:0' + CAST(H.run_duration AS CHAR(1))  
        WHEN 2 THEN '00:00:' + CAST(H.run_duration AS CHAR(2))  
        WHEN 3 THEN '00:0' + STUFF(CAST(H.run_duration AS CHAR(3)), 2, 0, ':')  
        WHEN 4 THEN '00:' + STUFF(CAST(H.run_duration AS CHAR(4)), 3, 0, ':')  
        WHEN 5 THEN '0' + STUFF(STUFF(CAST(H.run_duration AS CHAR(5)), 2, 0, ':'), 5, 0, ':')  
        WHEN 6 THEN STUFF(STUFF(CAST(H.run_duration AS CHAR(6)), 3, 0, ':'), 6, 0, ':')  
      END AS dureeExecutionEtape
      , CASE H.run_status  
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'SUCCESS'
        WHEN 2 THEN 'RETRY'
        WHEN 3 THEN 'CANCELED'
        WHEN 4 THEN 'RUNNING' -- Pas fiable
      END AS statutExecutionEtape
  FROM    msdb.dbo.sysjobs AS J  
  INNER JOIN  msdb.dbo.sysjobsteps AS S  
        ON J.job_id = S.job_id  
  INNER JOIN  msdb.dbo.sysjobhistory AS H  
        ON S.job_id = H.job_id  
        AND S.step_id = H.step_id  
  LEFT JOIN  sys.sysmessages AS M  
        ON H.sql_message_id = M.error  
) AS JOB_CHARACTERISTICS (nomJob, nomEtapeJob, numeroEtapeJob, msgEtape, dateExecutionEtape, dureeExecutionEtape, statutExecutionEtape)

ElSuket

2 réflexions au sujet de « Voir l’historique d’exécution des jobs par une requête »

  1. Bonjour,

    Pouvez-vous essayer sans le « sys. » qui précède « sysmessages »? Je n’ai pas d’instance SQL Server 2000 sous la main pour tester … :)

    N’hésitez pas à donner le label de l’erreur ;)

    @++ ;)

  2. Bonjour,

    Merci pour ton script.
    Je suis débutant sous sql server. La version complete marche très bien sous sql server 2008 mais sous sql server 2000 j’ai un message d’erreur sur sysmessages ?

    Comment pourrais je résoudre ce problème.

    Merci

Laisser un commentaire