September 28, 2009 at 6:59 am
I've got a proc that gives me my SQL Server jobs and the times they run. I'd like to take the results and turn it into a Gantt chart so I have a physical view of when jobs run. Has anyone ever done this before?
I'd appreciate any thoughts on what direction I should go with this. Thanks,
October 7, 2009 at 8:58 am
I've was just wishing for the same thing myself! We've got so many jobs on so many servers, that would be really useful.
I'm not a SSRS expert, but I bet if it has a gantt chart option you could probably come up with a query to feed it what it needs if you have the time to ponder it.
The Redneck DBA
October 8, 2009 at 7:07 am
I haven't done this myself but the first thing that comes to mind is that if you have a handle on the job's key information like the name & start\end dates\times and could get that info to Excel, you could then import the Excel info into MS project and maybe use it's Gantt reporting feature? Just a thought.
maddog
October 8, 2009 at 7:24 am
Can't remember where I saw this, really need to keep better track, but if you use replicate on the datediff, you can get this in a text format.
here's what I have so far, need to break it up so the results show the actual days/hours/whatever, working on that part still:
IF object_id('tempdb..#jobs') IS NOT NULL BEGIN DROP TABLE #jobs END
CREATE TABLE #jobs (job char(4),
starttime datetime,
endtime datetime)
INSERT INTO #jobs VALUES ('job1','1/1/2009 00:00:00','1/1/2009 04:00:00')
INSERT INTO #jobs VALUES ('job2','1/1/2009 02:00:00','1/2/2009 01:15:00')
INSERT INTO #jobs VALUES ('job3','1/2/2009 03:00:00','1/5/2009 00:05:00')
INSERT INTO #jobs VALUES ('job4','1/1/2009 10:00:00','1/4/2009 12:00:00')
DECLARE @startMonitoring datetime, @endMonitoring datetime
-- Set your @startMonitoring and @endMonitoring values to the timeframe you want to view.
--Defaults here are min and max of the days involved
SELECT @startMonitoring = dateadd(dd,datediff(dd,0,min(starttime)),0),
@endMonitoring = dateadd(dd,datediff(dd,0,max(endtime)+1),0)
FROM #jobs
-- Change the unit of measurement in the datediff below to see more or less detail if desired (minutes/days/etc)
SELECT job,
replicate(' ',datediff(hh,@startMonitoring,starttime))
+replicate('X',datediff(hh,starttime,endtime))
+replicate(' ',datediff(hh,endtime,@endMonitoring)) AS Gantt
FROM #jobs
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 8, 2009 at 7:41 am
Jut did a quick test on my Excel to MS Project import suggestion and it worked satisfactorily to me anyway.
1) Created & saved an Excel file with three columns:
Task Name - formatted as text)
Start - formatted as date mm/dd/yy hh:mm (24 hr format)
Duration - hardcoded as '15m' since you probably dont' know the exact runtimes each jobs has and you're concerned with start times mostly anyway
2) Open MS Project and Clicked 'File > Open' and selected the Excel file saved in step 1. Followed the mport mapping wizard, mapping from Excel to MSProject fields as follows:
Name = Task Name
Start = Start
Duration = Duration
Note: you can save this mapping in MS Project for future use
3) After importing, went to the default Gantt chart on the right and changed the timescale to have 3 tiers, top tier = weeks, middle = days, bottom = hours.
To my eyes gave a nice simple representation of the jobs and start timing relationships. I'm sure much or all of this could be automated with a little DTS\SSIS, MSWin shell commands or OLE scripting and the saved project mapping.
maddog
October 8, 2009 at 9:20 am
Hi Brandie,
I submitted the following script a while ago which might help you out. Like the post above, it uses the REPLICATE function.
http://www.sqlservercentral.com/Contributions/Edit/66476
HTH,
Martin
October 8, 2009 at 9:44 am
webtekkie (10/8/2009)
Hi Brandie,I submitted the following script a while ago which might help you out. Like the post above, it uses the REPLICATE function.
http://www.sqlservercentral.com/Contributions/Edit/66476
HTH,
Martin
Martin, link's no good if we're not you, can't view your contributions as non-authors. Care to post it to the thread as an attachment?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 8, 2009 at 10:13 am
Oops! sorry! 😛
October 8, 2009 at 10:39 am
Nice, thanks!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 19, 2009 at 5:48 am
Thanks, Everyone. Sorry I couldn't reply sooner. I was out on vacation.
What I'm working on is a Weekly chart that shows what day of the week & time that the job runs. Today I'll take a look at the solutions that have been posted and let you know if any of them work for me.
October 19, 2009 at 5:59 am
Ahh. I think everyone misunderstands my problem. I have my code. I also have job duration from a JobsMonitoring job we have. Now, I just need to translate my code into a Gantt Chart.
I'm trying to resist using a manual method of importing into MS Project. I need to make it dynamic so that when we add or delete jobs, we don't have to go through the whole process of creating the report from scratch.
October 19, 2009 at 6:43 am
So.... are you asking that it reside in Project, but not require manual steps to get it there?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 19, 2009 at 11:20 am
If there's a way to do that, yes.
But if another of the SQL Tools does Gantt Charts (besides Project), I'd like to do that too.
October 20, 2009 at 8:05 am
Well, although I was having fun playing with it, I wasn't getting much further than the original solution I provided.
And then I ran across this, which should address your problem nicely:
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/Santa+gets+an+RDL+present/61782/
enjoy!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
January 14, 2010 at 12:40 pm
Brandie, more fun, but this is a separate app (I haven't used it, but looks interesting:
http://www.sqlsoft.co.uk/sqljobvis.php
... and a Connect item to include something like this in SQL Server Agent...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply