|
DECLARE @Yesterday AS datetime |
|
SET @Yesterday = DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0) |
|
|
|
-- Running Jobs |
|
SELECT job.Name, |
|
job.job_ID, |
|
job.Originating_Server, |
|
activity.run_requested_Date, |
|
DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) AS Elapsed |
|
FROM msdb.dbo.sysjobs_view job |
|
INNER JOIN msdb.dbo.sysjobactivity activity |
|
ON (job.job_id = activity.job_id) |
|
WHERE run_Requested_date IS NOT NULL AND stop_execution_date IS NULL |
|
AND job.name IN ('01.DEL','02.Import','03.Daily Job') |
|
|
|
-- Fail Jobs |
|
SELECT |
|
j.name AS 'JobName', |
|
s.step_id AS 'Step', |
|
s.step_name AS 'StepName', |
|
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' |
|
FROM msdb.dbo.sysjobs j |
|
INNER JOIN msdb.dbo.sysjobsteps s |
|
ON j.job_id = s.job_id |
|
INNER JOIN msdb.dbo.sysjobhistory h |
|
ON s.job_id = h.job_id |
|
AND s.step_id = h.step_id |
|
AND h.step_id <> 0 |
|
WHERE j.enabled = 1 |
|
AND j.name IN ('01.Del','02.Import','03.Process') |
|
AND msdb.dbo.agent_datetime(run_date, run_time) >= @Yesterday |
|
AND h.run_status <> 1 |
|
ORDER BY JobName, RunDateTime DESC |
|
|
|
-- Show Single Job Total Time |
|
SELECT |
|
j.name as 'JobName', |
|
msdb.dbo.agent_datetime(h.run_date, h.run_time) as 'RunDateTime', |
|
(SUBSTRING(RIGHT('0000000'+CAST(run_duration as varchar),6),1,2)) as 'RunDurationHours', |
|
(SUBSTRING(RIGHT('0000000'+CAST(run_duration as varchar),6),3,2)) as 'RunDurationMinutes', |
|
(SUBSTRING(RIGHT('0000000'+CAST(run_duration as varchar),6),5,2)) as 'RunDurationSeconds' |
|
FROM msdb.dbo.sysjobhistory h |
|
LEFT JOIN msdb.dbo.sysjobs j |
|
ON j.job_id = h.job_id and step_id =0 |
|
WHERE j.enabled = 1 |
|
AND j.name = '01.Del' |
|
AND msdb.dbo.agent_datetime(run_date, run_time) >= @Yesterday |
|
|
|
-- Show Single Job Each Setp Time |
|
SELECT |
|
j.name as 'JobName', |
|
s.step_id as 'Step', |
|
s.step_name as 'StepName', |
|
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime', |
|
(SUBSTRING(RIGHT('0000000'+CAST(run_duration as varchar),6),1,2)) as 'RunDurationHours', |
|
(SUBSTRING(RIGHT('0000000'+CAST(run_duration as varchar),6),3,2)) as 'RunDurationMinutes', |
|
(SUBSTRING(RIGHT('0000000'+CAST(run_duration as varchar),6),5,2)) as 'RunDurationSeconds' |
|
FROM msdb.dbo.sysjobs j |
|
INNER JOIN msdb.dbo.sysjobsteps s |
|
ON j.job_id = s.job_id |
|
INNER JOIN msdb.dbo.sysjobhistory h |
|
ON s.job_id = h.job_id |
|
AND s.step_id = h.step_id |
|
AND h.step_id <> 0 |
|
WHERE j.enabled = 1 |
|
AND j.name = '03.Process' |
|
AND msdb.dbo.agent_datetime(run_date, run_time) >= CONVERT(CHAR(10), GETDATE(), 20) |
|
AND s.step_id IN (2,4,5) |
|
ORDER BY Step |
