January 11, 2008 at 10:01 am
I'm trying to get the last run date/time using the following query but it returns NULL in somecolumns despite the fact that those jobs appear in Enterprise Manager WITH a date and time for last run.
Any thoughts?
SELECT
j.[name]
,j.job_id
,h.message
,h.run_date
,h.run_Time
,h.run_duration
,s.step_name
,s.command
,s.database_name
FROM
sysjobs j
LEFT JOIN sysjobhistory h
ON h.job_id = j.job_id
LEFT JOIN sysjobsteps s
ON s.job_id = j.job_id
WHERE
j.[name] LIKE 'merge%'
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 11, 2008 at 10:51 am
If you look at the code of mdsb..sp_help_jobhistory, many things will become clearer 🙂
There is table sysjobservers that holds last run time of a job.
...and your only reply is slàinte mhath
January 11, 2008 at 10:58 am
Thanks!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 11, 2008 at 1:06 pm
--try this, maybe more than you asked for, but works in my environment
SELECT
@@serverName as 'Server Name',
left(J.Name, 50) AS 'Job Name',
'Last Run Stat'= isnull(Case when Q2.run_status = 1 then 'Succeeded'
when Q2.run_status = 0 then 'Failed'
when Q2.run_status = 2 then 'Retry'
when Q2.run_status = 3 then 'Canceled'
when Q2.run_status = 4 then 'Running'
End,'NA'),
'Last Run Date' = isnull (
CASE q2.run_date
WHEN 0 THEN 'N/A'
ELSE substring(convert(varchar(15),q2.run_date),1,4) + '/' +
substring(convert(varchar(15),q2.run_date),5,2) + '/' +
substring(convert(varchar(15),q2.run_date),7,2)
end, 'N/A'),
'Last Run Time' = isnull(
CASE len(q2.run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(q2.run_time,3),1)
+':' + right(q2.run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(q2.run_time,5),1)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(q2.run_time,6),2)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
END, 'NA'),
'Job Enab' = CASE J.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Enab' = CASE jS.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Freq' = CASE js.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Occurs'= js.freq_subday_interval,
'Sub Freq Interval'= Case js.freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
End,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
isnull(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,'NA') as 'Avg Duration',
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q2.[Last Duration],3),1)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q2.[Last Duration],5),1)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration'
FROM MSDB.dbo.sysjobs J
LEFT OUTER JOIN MSDB.dbo.sysjobschedules JS
ON J.job_id = JS.job_id
--Left outer join msdb.dbo.sysschedules s
-- on JS.schedule_id = s.schedule_id
LEFT OUTER JOIN (SELECT job_id, avg(run_duration) AS run_duration
FROM MSDB.dbo.sysjobhistory
GROUP BY job_id) Q1
ON J.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.dbo.sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON j.job_id = Q2.job_id
WHERE Next_run_time = 0
UNION
SELECT @@serverName as 'Server Name',
left(j.Name, 50) AS 'Job Name',
'Last Run Stat' = isnull(Case when Q2.run_status = 1 then 'Succeeded'
when Q2.run_status = 0 then 'Failed'
when Q2.run_status = 2 then 'Retry'
when Q2.run_status = 3 then 'Canceled'
when Q2.run_status = 4 then 'Running'
End,'NA'),
'Last Run Date' = isnull (
CASE q2.run_date
WHEN 0 THEN 'N/A'
ELSE substring(convert(varchar(15),q2.run_date),1,4) + '/' +
substring(convert(varchar(15),q2.run_date),5,2) + '/' +
substring(convert(varchar(15),q2.run_date),7,2)
end, 'N/A'),
'Last Run Time' = isnull(
CASE len(q2.run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(q2.run_time,3),1)
+':' + right(q2.run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(q2.run_time,5),1)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(q2.run_time,6),2)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
END, 'NA'),
'Job Enab' = CASE j.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Enabled' = CASE j.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Freq' = CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Occurs'=freq_subday_interval,
'Sub Freq Interval'= Case freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
End,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = isnull(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,'NA'),
isnull(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,'NA') as 'Avg Duration',
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q2.[Last Duration],3),1)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q2.[Last Duration],5),1)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration'
FROM MSDB.dbo.sysjobs J
LEFT OUTER JOIN MSDB.dbo.sysjobschedules JS
ON j.job_id = JS.job_id
--Left outer join msdb.dbo.sysschedules s
-- on JS.schedule_id = s.schedule_id
LEFT OUTER JOIN (SELECT job_id, avg(run_duration) AS run_duration
FROM MSDB.dbo.sysjobhistory
GROUP BY job_id) Q1
ON j.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status, T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.DBO.sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON j.job_id = Q2.job_id
WHERE Next_run_time <> 0
ORDER BY 1,4,5, 2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply