Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Job Summary

By Adnan Korkmaz,

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

Total article views: 1842 | Views in the last 30 days: 8
 
Related Articles
FORUM

Last executed query total duration

Last executed query total duration

FORUM

Same SQL statement executed with Different duration

Every time Store procedure take different duration to exeucte

FORUM

SQL Server 2005 Profiler

Duration

FORUM

SQL 2005 - Long duration Stored Procedure

SQL 2005 - Long duration Stored Procedure

ARTICLE

Working with SQL Agent Durations

SQL Agent stores duration in HHMMSS format - not always useful. Discover how to use Powershell, some...

Tags
jobs    
monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones