July 11, 2011 at 2:57 am
Hi
I want to create a report that not only show me what jobs are running, failed and so on, but also create a gantt chart based on start time and duration.
I have created a code that does this, but only if I have one instance per job per day.
I have several jobs that run each hour, and my current code will use an aggeregate and just sum it all up to one job for that day.
Looking into sysjobhistory we find the collumn instance_id, but this is per job step, not an unique id key for each run of that singel job.
Any one got an idea on how to solve this?
Thanks.
Regards
Dan
July 11, 2011 at 8:54 am
Have you looked in the scripts section of SSC to see if there is a script you could use?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 12, 2011 at 12:57 am
Hi.
Yes I found some scripts here that I could use, but none that seem to solve this problem.
Dan
July 12, 2011 at 2:31 am
Dan-Ketil Jakobsen (7/12/2011)
Hi.Yes I found some scripts here that I could use, but none that seem to solve this problem.
Dan
Why don't you post the code you currently have and ask for the difference you need to add to it. Maybe it'll be less dauting for us that way.
July 12, 2011 at 3:36 am
of course...
Sorry..
--drop view dbo.jobber_siste_dag
create view dbo.jobber_siste_dag -- view that aggregates jobs and set min/max timestamp
as
select
distinct
job_id as jobb, -- job
[run_date] as dato, -- date
run_status as status,
min(convert(datetime,rtrim(run_date))+ (run_time*9+run_time%10000*6+run_time%100*10+25*run_duration)/216e4) as min_tid,
max(convert(datetime,rtrim(run_date))+ (run_time*9+run_time%10000*6+run_time%100*10+25*run_duration)/216e4) as max_tid
FROM msdb..sysjobhistory
where step_id <> 0
group by job_id,[run_date],status
truncate table[tillegg].[dbo].[jobb_schedule]
INSERT[tillegg].[dbo].[jobb_schedule]
(
Oppdatert, last_update
Start,
Stopp,
Jobb, --job name
Steg, -- step
Server,
år, - year
måned, --month
dag, -- day
uke, -- week
Time, -- hour
Forbruk, -- duration
status
)
select
c.dato,
c.min_tid,
c.max_tid,
rtrim(b.name),
count(distinct a.step_id),
'BPW10014',
substring(cast(c.dato as varchar),1,4),
substring(cast(c.dato as varchar),5,2),
substring(cast(c.dato as varchar),7,2),
datepart(week,(substring(cast(c.dato as varchar),1,4)+'-'+ substring(cast(c.dato as varchar),5,2) +'-'+ substring(cast(c.dato as varchar),7,2))),
datepart(hour,min_tid),
datediff(minute,min_tid,max_tid),
status
FROM
msdb..sysjobhistory as a
left outer join msdb..sysjobs as b ON
a.job_id = b.job_id
left outer join dbo.jobber_siste_dag as c ON
a.job_id = c.jobb
where
a.run_date = c.dato
and b.enabled = 1
group by B.name,c.dato,c.min_tid,c.max_tid,status
July 12, 2011 at 5:48 am
Whack the view and the min / max. That'll give you all the runs and return the data you need.
If this is not what you want then please post sample data & sample result based on the data.
July 13, 2011 at 8:36 am
get the free tool SQLJOBVIS.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2011 at 8:59 am
Wow that's so cool!
Any other gems like this you care to share?
July 13, 2011 at 9:29 am
I have collected a huge array of tools, scripts, etc. in my 15 years with SQL Server. I REALLY need to do a "tools I use" session for SQL Saturday's!
heres a few I use or recommend regularly: sql sentry plan explorer, sp_whoisactive, dbsophic's free tool, sql internals viewer, sqlbenmarkprolite, ola.hallengren.com mx suite, managepartition20.exe, sqlio, SSSM tools pack
non-database: textpad (LOVE this one), sysinternals, trillian, roboform, iarsn taskinfo, sourcegear vault (free single user)
only a few of those are commercial but they are all worth it
I have too many scripts to name but the 3 most important for my consulting are file IO stall analysis, wait stats analysis and a big 'server documentation' script.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2011 at 9:36 am
Big script like Brent Ozar's blitz?
July 13, 2011 at 10:50 am
similar to that
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2011 at 12:10 pm
TheSQLGuru (7/13/2011)
non-database: textpad (LOVE this one), sysinternals, trillian, roboform, iarsn taskinfo, sourcegear vault (free single user)
Kevin,
Glad you put in a plug for good ol' textpad. This one has saved my bacon more than once. It's one of the only text editors that will open a huge text file (CSV) for analysis and it can open these files in binary mode to make it easy to find garbage characters.
Todd Fifield
July 13, 2011 at 12:27 pm
TheSQLGuru (7/13/2011)
similar to that
Care to share (even if only in private)?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply