July 24, 2017 by Kenneth Fisher
Looking at what a job has been doing is important. Particularly if something is going wrong. Now there are several ways to do this depending on your preference. You can use the system views.
Honestly, though, as much as I love writing queries for things these views are more than a bit difficult to work with.
But sometimes what you want is an overall history. Maybe you want to see your longest running jobs? Or the most frequent jobs? Or jobs that are failing? There is a great pre-built report just for that.
Now, be warned. The report and the history view both pull from the system views and they only hold so much history. You need to make sure that you are aware of what your job history system settings are.
And of course, all of this is to view the job information across a single server. If you want to view aggregate data across multiple instances you’ll have to either roll your own solution using SSIS, Powershell, etc, or use a paid product like Idera’s SQL Server Job Manager, Redgate’s SQL Monitor or Minion Enterprise.