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.

• msdb.dbo.sysjobs
• msdb.dbo.sysjobactivity
• msdb.dbo.sysjobhistory
• msdb.dbo.sysjobsteps
• msdb.dbo.sysjobsteplogs

Honestly, though, as much as I love writing queries for things these views are more than a bit difficult to work with.

So let’s look at the GUI. If you need to look at the error for a job or it’s output, in detail, the best way is to use the View History option.

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.

Filed under: Microsoft SQL Server, Reporting, SQL Agent Jobs, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, Reporting, SQL Agent Jobs, SSMS