Este script é utilizado para informar o último status de execução de um Job
USE msdb
GO
/*
Consulta que informa o status da ultima execução de cada job
Esta deve ser executada diáriamente
*/
SELECT
Name,
CONVERT(DATETIME, MAX(lastrun)) AS [LastRun],
CASE len([run duration])
WHEN 1 THEN CAST('00:00:0' + CAST([run duration] AS CHAR) AS CHAR (8))
WHEN 2 THEN CAST('00:00:' + CAST([run duration] AS CHAR) AS CHAR (8))
WHEN 3 THEN CAST('00:0' + LEFT(RIGHT([run duration], 3), 1) + ':' + RIGHT([run duration], 2) AS CHAR (8))
WHEN 4 THEN CAST('00:' + LEFT(RIGHT([run duration], 4), 2) + ':' + RIGHT([run duration], 2) AS CHAR (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT([run duration], 5), 1) + ':' + LEFT(RIGHT([run duration], 4), 2) + ':' + RIGHT([run duration], 2) AS CHAR (8))
WHEN 6 THEN CAST(LEFT(RIGHT([run duration], 6), 2) + ':' + LEFT(RIGHT([run duration], 4), 2) + ':' + RIGHT([run duration], 2) AS CHAR (8))
END AS [RunDuration],
Status
FROM
(SELECT
j.[name],
CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') AS [LastRun],
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
,jh.run_date AS [last run date]
,jh.run_time AS [last run time]
,js.next_run_date AS [next run date]
,js.next_run_time AS [next run time]
,jh.run_duration AS [run duration]
FROM
msdb.dbo.sysjobs j LEFT OUTER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
WHERE
j.enabled = 1 AND CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') =
(SELECT
MAX(CONVERT(VARCHAR, t.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,t.run_time),6),5,0,':'),3,0,':'))
FROM
msdb.dbo.sysjobhistory t WHERE t.job_id = jh.job_id)
GROUP BY
j.name,
js.next_run_date,
js.next_run_time,
jh.run_date,
jh.run_time,
jh.run_status,
jh.run_duration) t
GROUP BY
t.name, t.status, t.[run duration]
ORDER BY
t.name