Comment voir l’historique d’exécution des jobs par une requête dans SQL Server ?

0
133

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

query execution
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

LEAVE A REPLY

Please enter your comment!
Please enter your name here