Showing posts with label Next Run Time. Show all posts
Showing posts with label Next Run Time. Show all posts

Monday, December 7, 2009

To Find List of Jobs, Last Run Status and Schedules

To find out lsit of jobs and thier details.

SELECT
J.Name
,CASE WHEN J.Enabled=1 THEN 'Enabled' ELSE 'Disabled' END AS JobEnableStatus
,SJS.Command AS Query
,SJS.Step_Name
,LastRunDate
,LastRunTime
,CASE WHEN LastRunStatus =1 THEN 'Success' ELSE 'Failed' END AS LastRunStatus
,Next_Run_Date
,CASE len(Next_Run_Time)
WHEN 3 THEN CAST('00:0'+ LEFT(RIGHT(Next_Run_Time,3),1)+':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 4 THEN CAST('00:'+ LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(Next_Run_Time,5),1) +':' + LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 6 THEN CAST(LEFT(RIGHT(Next_Run_Time,6),2) +':' + LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
END AS NextRunTime
,SS.Name AS SchduleFrequency
,CASE LEN(Q1.Run_Duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.Run_Duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.Run_Duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q1.Run_Duration,3),1)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q1.Run_Duration,5),1)
+':' + Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.Run_Duration,6),2)
+':' + Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
END as 'Job Ran Duration'
FROM MSDB..SYSJobs J
INNER JOIN MSDB..SysJobSchedules S ON J.job_id= S.job_id
LEFT JOIN MSDB..SYSJobSteps SJS ON SJS.job_id = S.job_id
LEFT JOIN MSDB..SysSchedules SS ON SS.Schedule_Id = S.Schedule_Id
LEFT JOIN (
SELECT
job_id
,MAX(run_duration) AS run_duration
,MAX(Run_Status) AS LastRunStatus
,MAX(Run_Date) AS LastRunDate
,CASE LEN(MAX(Run_Time))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(MAX(Run_Time),3),1)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(MAX(Run_Time),5),1)
+':' + LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 6 THEN CAST(LEFT(RIGHT(MAX(Run_Time),6),2)
+':' + LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) AS CHAR (8))
END AS LastRunTime


FROM MSDB..SysJobHistory
GROUP BY job_id
) Q1
ON J.job_id = Q1.job_id

WHERE Category_Id =0 AND J.Enabled=1

ORDER BY LastRunDate,LastRunTime,J.Name


Thanks,