Muller Jannie

About myself and everyone else.

SQL Agent – Job Gantt

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)!

Job History

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.

Happy camping.


One comment on “SQL Agent – Job Gantt

  1. mullerjannie
    August 16, 2011

    Question: In addition to running, you can also indicate failed jobs.
    Do you know how to do this this ? a Tip. the blue bar of running jobs are indicated by using the count function. Think about this for a second…

    Answer: Once you have read all the data, create a duplicate entry for the jobstep that failed with the date of failure. You can test this in SQL by doing a group by having count(*) > 2 on your data. When you run the pivot that row will give you a count(Fields!StepName,”Day”) of 2 as opposed to 1. You can now use a conditional to colour this “Red”.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


This entry was posted on August 16, 2011 by in SQL Server and tagged , , .
%d bloggers like this: