Pivot Job History
Pivot Job History Across multiple SQL Server Instances

I have several hundred SQL Servers, many of these are clusters of federated SQL Servers working together for one particular "thing".  I regularly need to check certain jobs on the server and also compare run times across servers.  This started due to a EMC Clone (BCV) job, that needed to checked across a particular farm.

We have a custom website called SQL Monitor (lame name alert), we wanted to display this job information, pivoted by runtime of the job and server, color coding it for success or failure and be able to drill into the information and pivot the step history of each job and it's average run time.

All the information is there, you just need to query it and display it.  Doing Pivots in SQL Server has gotten easier, but is still poor in my opionion, especially when you are handling an unknown number of data elements to pivot on.  I generally do not do pivots in SQL Server, but prefer to "offload" them to the application layer, and allow the application layer to complete the pivot. 

It provides a lot more control for the developer.  It is possible to do pivots in SQL Server, but for this particular requirement it would not have been (especially since it goes across multiple SQL Instances, I guess you could enter the world of linked servers, but when dealing with 100's of servers, that wouldn't be much fun).

Included is a simple Visual Studio 10 web project, that sets up a web page and shows the results.  The queries are all in there, though I've included a seperate file of the queries.  The Pivot is actually done in the c# code so, don't look for it in the TSQL.

Of particulare pain in the ass was setting up the query to to find the start time of each job and then getting the subsequent steps.  As the goal was to pivot by the start time, but each step in a particular job has it's own start time, and no real corresponding "key" back to the "start time".

Another pain was the run duration for each step is in the format of HHHmmss, so this requires parsing that value to handle time span calculations for average run time of each step.

I stayed away from using sp_help_job, as this stored procedure is way too "heavy" for such a simple query, and capturing the results of sp_help_job and then manipulating them, just increased complexity that wasn't necessary.