28
septembre
2010
Job Viewer : Monitoring des jobs d’une instance SQL Server
septembre
2010
Un article de zinzineti
Monitorer les jobs d’une instance SQL Server (2005 ou 2008) à l’aide du T-SQL
/************************************************************************************
— Description : Monitoring des jobs d’une instance SQL Server (2005 ou 2008)
— Auteur : Etienne ZINZINDOHOUE
************************************************************************************/
USE msdb
GO
-- Lister distinctement les différents jobs
With JOB_DISTINCT (id_job) AS
(
SELECT MAX(histo.instance_id) id_job
FROM sysjobhistory histo
GROUP BY (histo.job_id)
)
-- Afficher les différentes colonnes
SELECT
job.name AS [Nom Job],
Case run_status
WHEN '0' THEN 'Échec'
WHEN '1' THEN 'Succès'
WHEN '2' THEN 'Nouvelle tentative'
WHEN '3' THEN 'Annulé'
WHEN '4' THEN 'En cours d''exécution'
ELSE ''
END AS [Résultat Dernière Exécution],
CASE job.enabled
WHEN '1' THEN 'Oui'
ELSE 'Non'
END AS [Activé ?],
CAST (histo.run_date AS CHAR(8)) + ' '+
CASE LEN(CAST(histo.run_time AS CHAR(6)))
WHEN 1 THEN '00000' + CAST(histo.run_time AS CHAR(1))
WHEN 2 THEN '0000' + CAST(histo.run_time AS CHAR(2))
WHEN 3 THEN '000' + CAST(histo.run_time AS CHAR(3))
WHEN 4 THEN '00' + CAST(histo.run_time AS CHAR(4))
WHEN 5 THEN '0' + CAST(histo.run_time AS CHAR(5))
ELSE CAST(histo.run_time AS CHAR(6))
END AS [Date Heure Dernière Exécution],
histo.message AS [Message],
job.description AS [Description]
FROM sysjobhistory histo
INNER JOIN sysjobs job ON histo.job_id = job.job_id
WHERE histo.instance_id IN (SELECT JOB_DISTINCT.id_job FROM JOB_DISTINCT)
ORDER BY [Résultat Dernière Exécution]
GO
-- Lister distinctement les différents jobs
With JOB_DISTINCT (id_job) AS
(
SELECT MAX(histo.instance_id) id_job
FROM sysjobhistory histo
GROUP BY (histo.job_id)
)
-- Afficher les différentes colonnes
SELECT
job.name AS [Nom Job],
Case run_status
WHEN '0' THEN 'Échec'
WHEN '1' THEN 'Succès'
WHEN '2' THEN 'Nouvelle tentative'
WHEN '3' THEN 'Annulé'
WHEN '4' THEN 'En cours d''exécution'
ELSE ''
END AS [Résultat Dernière Exécution],
CASE job.enabled
WHEN '1' THEN 'Oui'
ELSE 'Non'
END AS [Activé ?],
CAST (histo.run_date AS CHAR(8)) + ' '+
CASE LEN(CAST(histo.run_time AS CHAR(6)))
WHEN 1 THEN '00000' + CAST(histo.run_time AS CHAR(1))
WHEN 2 THEN '0000' + CAST(histo.run_time AS CHAR(2))
WHEN 3 THEN '000' + CAST(histo.run_time AS CHAR(3))
WHEN 4 THEN '00' + CAST(histo.run_time AS CHAR(4))
WHEN 5 THEN '0' + CAST(histo.run_time AS CHAR(5))
ELSE CAST(histo.run_time AS CHAR(6))
END AS [Date Heure Dernière Exécution],
histo.message AS [Message],
job.description AS [Description]
FROM sysjobhistory histo
INNER JOIN sysjobs job ON histo.job_id = job.job_id
WHERE histo.instance_id IN (SELECT JOB_DISTINCT.id_job FROM JOB_DISTINCT)
ORDER BY [Résultat Dernière Exécution]
/************************************************************************************
— Description : Monitoring des jobs d’une instance SQL Server (2005 ou 2008)
— Auteur : Etienne ZINZINDOHOUE
************************************************************************************/
Monitorer les jobs de plusieurs instances SQL Serveur hébergées sur différentes machines