THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Power Pivot SQL Agent Job Monitor

In the last days I needed a quick and efficient way to monitor the performance of a job and of each step over time. There’s a lot of interesting data in MSDB database and the various sysjob tables, but the stored data is not really easy to use outside the Management Studio. Date and Time are not stored as date/time data type, there is no easy way to correlate the performance of a step with the related job execution (for example is not that easy to return all the steps of a job executed, say, yesterday at 10:00).

So I decided to write some views to make the querying easier and then the next move was to create a Tabular Model with Power Pivot on Excel to make also analysis easier.

Since this is a somehow recurring task that I find myself doing during my job – not often, but when I have to do it now I have something much more powerful in my hands – I decided to put it also con CodePlex so that that little work can be shared with the community.

You can find the scripts with the objects (3 UDFs and 4 Views) that must be created inside MSDB and the Power Pivot Excel 2010 model here:

http://jobmonitor.codeplex.com/

Enjoy!

Published Monday, January 7, 2013 11:23 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

spaghettidba said:

Thanks Davide!

Actually, I've been thinking for years that I needed to encapsulte those ugly date/time representations in a view and make it publicly available. I'm sure everyone would be keen to throw away that crap design, but I understand it would be a bloodbath for MS.

January 7, 2013 5:11 PM
 

Dirk Hondong said:

Hi Davide,

thanks for sharing.

I'll definitely try it the next days. Sounds very interesting.

Regards

Dirk

January 10, 2013 2:33 PM
 

Martin Garcia said:

Thank you Very much for sharing.

However, I have realized there is already an undocumented function in msdb: agent_datetime. It can be used for run_duration as well by passing 19000101 as date parameter.

Cheers.

Martin

April 25, 2016 10:23 PM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog

Syndication

Privacy Statement