Job Step Duration
-
I seem to spend a lot of time going up and down the EM View Job History window, trying to compare Step duration times. Obviously for a daily job that has about 100 steps it's hard to compare steps for today with (for example) a week ago.
Is there an easier way to do this?
Alan
-
how about :
declare
@daysago int
set
@daysago = 7
Select
j.name as JOBNAME
,
jh.step_id, jh.step_name
,
jh.run_date, jh.run_time, jh.run_duration
,
jh3.run_date, jh3.run_time, jh3.run_duration
FROM
msdb.dbo.sysjobhistory jh with(nolock)
inner join
msdb
.dbo.sysjobs j with(nolock)
on j.job_id = jh.job_id
inner
join
( select jh1.job_id, jh1.step_id, jh1.run_date, jh1.run_time, max((convert(bigint,jh2.run_date) * 1000000) + jh2.run_time) as FormarRunDateTime
from msdb.dbo.sysjobhistory jh1 with(nolock)
inner join msdb.dbo.sysjobhistory jh2 with(nolock)
on jh2.job_id = jh1.job_id
and jh2.step_id = jh1.step_id
and (convert(bigint,jh2.run_date ) * 1000000) + jh2.run_time < (convert(bigint,jh1.run_date - @daysago ) * 1000000) + jh1.run_time
where jh1.step_id > 0
group by jh1.job_id, jh1.step_id, jh1.run_date, jh1.run_time
) OldJobs
on
OldJobs.job_id = jh.job_id
and OldJobs.step_id = jh.step_id
and OldJobs.run_date = jh.run_date
inner join msdb.dbo.sysjobhistory jh3 with(nolock)
on jh3.job_id = OldJobs.job_id
and jh3.step_id = OldJobs.step_id
and jh3.run_date = convert(int,FormarRunDateTime/1000000)
and jh3.run_time = convert(int, substring(convert(char(14),FormarRunDateTime),9,6))
where
j.name like 'myjobnamepart%'
and jh.run_date = convert(int, replace(convert(char(10),dateadd(d, -1,getdate()), 121),'-',''))
order
by jh.run_date desc, jh.step_id
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
WOW!
Getting my head round this now..........
Thanks!
Alan
-
I second that
. -
Mine, too! I just ran it on an instance that is running replication, and get nothing... no rows returned. So, now, I really DO have to figure it out. Thanks for the starting place
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
Mine, too! I just ran it on an instance that is running replication, and get nothing... no rows returned. So, now, I really DO have to figure it out. Thanks for the starting place
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
Not my morning
First it was a lost post... I posted a long response to another entry, and it was GONE... and now, two of these. I think I will go walk on the beach. I can see the ocean from my chair, and the sun is shining, yes, must better I walk on the beach now.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
Okay, my head is clear... walk on the beach and lunch... and I still cannot make this query return anything... even when specifying "WHERE j.name like '%'", which should bring back everything.
What am I missing, pleas?
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
remove j.name like 'myjobnamepart%'
and try setting days ago to 1 or 14
-
there is also a litle pitfall with ...
and jh.run_date = convert(int, replace(convert(char(10),dateadd(d, -1,getdate()), 121),'-',''))
Meaning your job has been run the yesterday !
But it's best to give a startingdate !!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
or maybe even replace the complete where clause with
inner join
( select job_id, max(run_date) as Max_run_date
from msdb.dbo.sysjobhistory
group by job_id ) jhMAX
on jh.job_id = jhMAX.job_id
and jh.run_date = jhMAX.Max_run_date
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
tried that... down to '%', which should have done everything... also tried changing 7 to 1... but did not try 14... Looks like there's a new where clause on the thread... I will try that no.
This is going to be a handy tool
Thanks all!Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
Okay, this did not work either... I will start looking at the sys tables myself and see what I can come up with; but if anybody actually makes this work, please post the whole script. This could have saved me a lot of time yesterday... today it's taking time I don't have. Thanks Alzdba for the attempt! I do appreciate you very much.
David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
I've just copy/pasted the script and added the last inner-join-part.
It returned rows. (with sql2000 and with sql2005)
This is the full script, copy/paste it and run. It should give results if the jobs and jobsteps ran 7 days ago.
declare
@daysago int
set
@daysago = 7
Select
j.name as JOBNAME
,
jh.step_id, jh.step_name
,
jh.run_date, jh.run_time, jh.run_duration
,
jh3.run_date, jh3.run_time, jh3.run_duration
FROM
msdb.dbo.sysjobhistory jh with(nolock)
inner join
msdb
.dbo.sysjobs j with(nolock)
on j.job_id = jh.job_id
inner
join
( select jh1.job_id, jh1.step_id, jh1.run_date, jh1.run_time, max((convert(bigint,jh2.run_date) * 1000000) + jh2.run_time) as FormarRunDateTime
from msdb.dbo.sysjobhistory jh1 with(nolock)
inner join msdb.dbo.sysjobhistory jh2 with(nolock)
on jh2.job_id = jh1.job_id
and jh2.step_id = jh1.step_id
and (convert(bigint,jh2.run_date ) * 1000000) + jh2.run_time < (convert(bigint,jh1.run_date - @daysago ) * 1000000) + jh1.run_time
where jh1.step_id > 0
group by jh1.job_id, jh1.step_id, jh1.run_date, jh1.run_time
) OldJobs
on
OldJobs.job_id = jh.job_id
and OldJobs.step_id = jh.step_id
and OldJobs.run_date = jh.run_date
inner join msdb.dbo.sysjobhistory jh3 with(nolock)
on jh3.job_id = OldJobs.job_id
and jh3.step_id = OldJobs.step_id
and jh3.run_date = convert(int,FormarRunDateTime/1000000)
and jh3.run_time = convert(int, substring(convert(char(14),FormarRunDateTime),9,6))
inner
join
( select job_id, step_id, max((convert(bigint,run_date) * 1000000) + run_time) as MaxRunDateTime, max(run_date) as Max_run_date
from msdb.dbo.sysjobhistory
group by job_id, step_id ) jhMAX
on
jh.job_id = jhMAX.job_id
and
jh.step_id = jhMAX.step_id
and
jh.run_date = jhMAX.Max_run_date
and
jh.run_date = convert(int,jhMAX.MaxRunDateTime/1000000)
and
jh.run_time = convert(int, substring(convert(char(14),jhMAX.MaxRunDateTime),9,6))
-- where j.name like 'myjobnamepart%'
-- and jh.run_date = convert(int, replace(convert(char(10),dateadd(d, -1,getdate()), 121),'-',''))
order
by JOBNAME, jh.run_date desc, jh.step_id
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanks! this works. I do appreciate it very much.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply