Job Summary

Some background: I am not a DBA and it's my first contribution here. Sorry for any typos / errors if any in advance. I develop an application which runs on several SQL servers where I have many jobs with different schedules on each server. To be able to monitor all these servers at once, I wrote an Asp page which executes this script and summarizes the results.

Installation: You can create the script in whatever database you like, while I preferred master.

Parameters: (All parameters are optional)

• @JobName: The exact name of the Job you want to filter. If omitted, all mathing jobs listed.
• @ShowDisabled: Inludes or excludes disabled jobs.
• @ShowUnscheduled: Includes or excludes jobs who have no schedule at all.
• @JobThresholdSec: If this parameters is positive, shows only the jobs with Last duration above it.
• @AvgExecThresholdSec: If this parameters is positive, shows only the jobs with Average duration above it.

Output Columns:

• JobName : Name of the job
• LastStep : The last step executed / currently executing
• Info : General information about the status of the job (Running, Disabled, No steps, Not scheduled, Never Run, Failed, Succeeded, Retry, Canceled or Unknown)
• DatabaseName : The db name of the job
• Enabled, ScheduleCount, StepCount : Obvious I guess.
• StartDate : Execution start
• FinishDate : Execution end (if ended)
• DurationSec : Seconds between StartDate and FinishDate. If currently running, between StartDate and GetDate(). (i.e. 3828)
• DurationSecFormatted : DurationSec above, human-readably formatted (i.e. 01:03:48)
• avgDurationSec : Average execution time in seconds.
• avgDurationSecFormatted : avgDurationSec above, formatted in hh:mm:ss (i.e. 02:19:12)
• DurationRatio : The current duration divided by average duration, as a percentage. This might give hint that something might be wrong with the job, if above a threshold of your own. For example a DurationRatio of 140 means that the job is taking / has taken a time 40 percent longer than it normally used to.
• NextRunDate : The next time the job will run. 
• StepCommand : The text for the step (SQL / command line, etc) Becomes handy if you have really many jobs, whose details can't be understood from its name.
• HistoryMessage: The last history message.

Samples:

Default usage lists all enabled jobs with a schedule:

• exec JobSummaryUtil

You can filter to see only one job at a time:

• exec JobSummaryUtil 'syspolicy_purge_history'

List all jobs:

• exec JobSummaryUtil null, 1, 1

List all jobs, whose last execution time is above 1 minute:

• exec JobSummaryUtil null, 1, 1, 60

List all jobs, whose average execution time is above 1 hour:

• exec JobSummaryUtil null, 1, 1, 0, 3600