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 |
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 |
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) |
USE msdb
GO
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT(‘000000′ + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,’:’),3,0,’:’) RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then ‘failed’
when 1 then ‘Succeded’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name, h.run_date, h.run_time
GO
| Column | Description |
|---|---|
| [JobName] | Name of job as specified |
| [StepName] | Name of step as specified |
| [RunDate] | Date when job run |
| [RunTime] | Time when job run |
| [StepDuration] | Duration in seconds that a step took to complete |
| [ExecutionStatus] | Execution status of step |
| [MessageGenerated] | Message generated at end of step |
