About myself and everyone else.
Now that I have the idea how to design a Gantt chart using SQL and reporting services I thought about other uses for this.
I decided I’ll create a chart for my SQL server agent. This will indicate the runtime of my jobs across n number of servers. Using this method I can see when my quiet times are for the agent and when I should stagger jobs or where I should move them to new schedules. I can see long running jobs or different timings (runtime) for the same job on mutliple servers.
Below the visuals.
So you can see from this I have the list of jobs and their steps in the tablix. This I did some conditional formatting to turn idle status green and blue for running. The code for this is much simpler than the previous post because you work with history data, there is also no need to calc into the future or do any dependancy checks. I have also indicated when more than 1 job runs at the same time with the active job count. This is slightly off as it’s currently counting idle as a job. I’m still to pay around with the filtering in the tablix to exclude the count(Fields!StepName) it it’s “Idle”. I’m having an issue with the scope.
As for the code I’ll attach this. Have a look and I hope you can use it!
Files below: Note I have no idea how to attach txt files or RDL in this blog. So this is a standard windows zip file renamed to doc. Rename it back and unzip. I’ve tested it (works on my PC)!
The zip file contains the SQL and the RDL, create a Datasource and set and tada!
BTW, I’m trying to use different fonts, bold etc. to hopefully make reading easier.