THE SQL Server Blog Spot on the Web

Welcome to - 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:


Published Monday, January 07, 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



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.



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.



April 25, 2016 10:23 PM

Leave a Comment


About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog


Privacy Statement