Voici une petite requête pour savoir quelle est la dateheure de fin des vos jobs planifiéss dans l’Agent SQL Server
WITH
T0 AS
(
SELECT instance_id,
run_date, run_time, run_duration,
CONVERT(DATETIME, CAST(run_date AS CHAR(8)), 112) AS RUN_D, CAST(run_time AS CHAR(6)) AS RUN_T, CAST(run_duration AS CHAR(6)) AS DUR
FROM msdb.dbo.sysjobhistory
),
T1 AS
(
SELECT *,
CASE LEN(RUN_T)
WHEN 1 THEN '00:00:0' + RUN_T
WHEN 2 THEN '00:00:' + RUN_T
WHEN 3 THEN '00:0' + SUBSTRING(RUN_T, 1, 1) + ':' + SUBSTRING(RUN_T, 2, 2)
WHEN 4 THEN '00:' + SUBSTRING(RUN_T, 1, 2) + ':' + SUBSTRING(RUN_T, 3, 2)
WHEN 5 THEN '0' + SUBSTRING(RUN_T, 1, 1) + ':' + SUBSTRING(RUN_T, 2, 2) + ':' + SUBSTRING(RUN_T, 4, 2)
WHEN 6 THEN SUBSTRING(RUN_T, 1, 2) + ':' + SUBSTRING(RUN_T, 3, 2) + ':' + SUBSTRING(RUN_T, 5, 2)
END AS RUN_TC,
CASE LEN(DUR)
WHEN 1 THEN CAST(DUR AS INT)
WHEN 2 THEN CAST(DUR AS INT)
WHEN 3 THEN CAST(SUBSTRING(RUN_T, 1, 1) AS INT) * 60 + CAST(SUBSTRING(RUN_T, 2, 2) AS INT)
WHEN 4 THEN CAST(SUBSTRING(RUN_T, 1, 2) AS INT) * 60 + CAST(SUBSTRING(RUN_T, 3, 2) AS INT)
WHEN 5 THEN CAST(SUBSTRING(RUN_T, 1, 1) AS INT) * 3660 + CAST(SUBSTRING(RUN_T, 2, 2) AS INT) * 60 + CAST(SUBSTRING(RUN_T, 4, 2) AS INT)
WHEN 6 THEN CAST(SUBSTRING(RUN_T, 1, 2) AS INT) * 3660 + CAST(SUBSTRING(RUN_T, 3, 2) AS INT) * 60 + CAST(SUBSTRING(RUN_T, 5, 2) AS INT)
END AS DUR_SECONDE
FROM T0
),
T2 AS
(
SELECT *, CAST(RUN_D + ' ' + RUN_TC AS DATETIME) AS RUN_DT
FROM T1
)
SELECT j.job_id, j.name AS JOB_NAME, RUN_DT AS DEBUT, DUR_SECONDE AS DUREE_SECONDE, DATEADD(second, DUR_SECONDE, RUN_DT) AS FIN
FROM T2
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON T2.instance_id = jh.instance_id
INNER JOIN msdb.dbo.sysjobs AS j
ON jh.job_id = j.job_id
WHERE step_id = 0
ORDER BY 2, 3 DESC;
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Merci
Il faut la jouer dans le contexte de la bonne base en l’occurrence celle de l’agent SQL qui est msdb.
Soit en faisant un USE msdb préventif, soit en préfixant les tables dbo.sysjobhistory et dbo.sysjobs.
Je rectifie en précisant les préfixes de bases.
Cette requête fonctionne pour les version 2005, 2008 et 2008 R2 (et probablement 2012).
A +
Bonjour,
J’ai voulu tester cela et j’obtiens ceci :
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dbo.sysjobhistory’.
Je n’ai pas vu d’indication de la version de SQL SERVER pour laquelle ce script est prévu. Je suis sur SQL SERVER 2005. Le problème vient p-e de là .