February 4, 2012 at 3:53 am
Hi ,
Can any one tell me how to know the duration of job that was run last time.
February 4, 2012 at 4:33 am
Have a look at sp_help_jobactivity.
August 19, 2013 at 7:02 am
Anyone have a "working query" to render AVG run_duration for a specific Jobname in SQL 2008 R2? I cannot seem to get this query working:
select
j.name as 'Jobname',
Replicate('0',(6-len(Cast(Avg(jh.run_duration) as varchar(6))))) + Cast(avg(jh.run_duration) as varchar(6)) as 'AVG_Runtime'
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
WHERE j.enabled = 1 --Only Enabled Jobs
and J.name = 'MY_Specific_Jobname_here'
group by jh.job_id, Replicate('0',(6-len(Cast(Avg(jh.run_duration) as varchar(6))))) + Cast(avg(jh.run_duration) as varchar(6)) as 'AVG_Runtime'
thx for any help!
August 19, 2013 at 7:19 am
This seems to work:
use msdb;
GO
select
d.jobname
,d.servername
, avgDurationMinutes=avg(d.durationMinutes)
, daydate=convert(char(10),startdatetime,101)
from (
select
jobname=j.name
,servername=server
,startdatetime=
CONVERT (DATETIME, RTRIM(run_date))
+ (
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
) / 216e4
, durationMinutes=
(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
)/60.
,enddatetime =
dateadd
(ss,
(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
)
,
(CONVERT (DATETIME, RTRIM(run_date))
+ (
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
) / 216e4 )
)
, retries_attempted
from sysjobs j (nolock)
join sysjobhistory h on
h.job_id = j.job_id
and h.step_id = 0 -- look only at the job outcome step for the total job runtime
where
j.name in ('CSG Replication Slot 1') -- Set the jobname here
--j.name in ('<strong>JobName</strong>') -- Set the jobname here
) d
where
datepart(dw,startdatetime)=7 -- Set your day of week here if desired. 7=Saturday
group by
d.jobname
,servername
,convert(char(10),startdatetime,101)
order by
d.jobname
,servername
,cast(convert(char(10),startdatetime,101)as datetime) desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy