select @@SERVERNAME as SQL_Server, job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where h.step_id = 0
) t
) t
order by job_name, run_datetime
1 則留言:
--Here is new one:
USE msdb
GO
SELECT
j.[name] AS [JobName],
run_status = CASE h.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END,
--h.run_date AS [LastRunDate(YYYYMMDD)],
--convert(varchar, convert(datetime, h.run_date), 111) AS [LastRunDate(YYYYMMDD)],
--convert(datetime, h.run_date) AS [LastRunDate(YYYYMMDD)],
CONVERT(varchar,(CONVERT(datetime, CONVERT(CHAR(8), h.run_date))),111) AS [LastRunDate(YYYY/MM/DD)],
--Right(('000000'+ cast(h.run_time as varchar)),6) AS [LastRunTime(MMDDSS)],
left(Right(('000000'+ cast(h.run_time as varchar)),6),2) + ':' + substring(Right(('000000'+ cast(h.run_time as varchar)),6),3,2) + ':' + right(Right(('000000'+ cast(h.run_time as varchar)),6),2) AS [LastRunTime(MM/DD/SS)],
--Right(('000000'+ cast(h.run_duration as varchar)),6) as [JobDuration(MMDDSS)],
left(Right(('000000'+ cast(h.run_duration as varchar)),6),2) + ':' + substring(Right(('000000'+ cast(h.run_duration as varchar)),6),3,2) + ':' + right(Right(('000000'+ cast(h.run_duration as varchar)),6),2) as [JobDuration(MM/DD/SS)]
FROM sysjobhistory h
INNER JOIN sysjobs j ON h.job_id = j.job_id
WHERE j.enabled = 1
--AND h.instance_id IN
--(SELECT MAX(h.instance_id)
-- FROM sysjobhistory h GROUP BY (h.job_id))
and j.[name] like '%Maintenance_Weekly'
--and h.run_time = '220000'
and h.step_id = '0'
GO
張貼留言