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.

Surprise, Taurus delivered my 992 back to me, actually tried yesterday but i wasn't home.

Pretty quick turn around time.

Taurus CEO claims to be delivering on increased customer service, and my experience agrees with that.  Sometimes all guns have issues and need to go back for work, I've had Smith & Wesson and Springfield Armory issues in the past as well, and they make up for it with great speed and customer service, looks like Taurus figured it out too !

So far the repair looks great, just went out back and put 50 rounds through it, not one issue.

The repair order states the cylinder stop was not adjusted properly and the tigger not adjusted properly, basically the cylinder timing was off.

All good now,

Thanks Taurus !

I've given up on the Tracker 992.

I contacted Customer Service, and I'd rate the experience as excellent.

I waited less than 10 minutes, had a Fed-Ex shipping label in less than 15 minutes and on it's way back to Taurus.

We'll see how long it takes them to repair, honestly I don't care if it takes awhile, as long it's fixed.

The gun had a lot of issues with the 22 mag cylinder locking up when shooting double action, it did this occasionally with the 22lr cylinder as well.  Something is wrong with it.

You can't beat a lifetime warranty.