Technical Article

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
CREATE PROCEDURE JobSummaryUtil
@JobName VARCHAR(255) = null, -- Optional job name filter
@ShowDisabled bit = 0, -- Include disabled jobs?
@ShowUnscheduled bit = 0, -- Include Unscheduled jobs?
@JobThresholdSec INT = 0, -- If positive, show only the jobs with LAST duration above this.
@AvgExecThresholdSec INT = 0 -- If positive, show only the jobs with AVERAGE duration above this.
AS
SELECT *
FROM 
( 
 SELECT JobName, ISNULL(LastStep,'') LastStep,
 CASE WHEN StartDate IS NOT NULL AND FinishDate IS NULL THEN 'Running' 
 WHEN Enabled = 0 THEN 'Disabled' 
 WHEN StepCount = 0 THEN 'No steps' 
 WHEN RunStatus IS NOT NULL THEN RunStatus 
 WHEN ScheduleCount = 0 THEN 'Not scheduled' 
 ELSE 'UNKNOWN' END Info,
 DatabaseName, Enabled, ScheduleCount, StepCount, 
 StartDate, FinishDate, DurationSec, 
 RIGHT('0'+convert(varchar(5),DurationSec/3600),2)+':'+RIGHT('0'+convert(varchar(5),DurationSec%3600/60),2)+':'+ RIGHT('0'+convert(varchar(5),(DurationSec%60)),2) DurationSecFormatted, 
 avgDurationSec,
 RIGHT('0'+convert(varchar(5),avgDurationSec/3600),2)+':'+RIGHT('0'+convert(varchar(5),avgDurationSec%3600/60),2)+':'+ RIGHT('0'+convert(varchar(5),(avgDurationSec%60)),2) avgDurationSecFormatted, 
 CASE WHEN (DurationSec IS NULL OR ISNULL(avgDurationSec, 0) = 0) THEN 0 ELSE CONVERT(DECIMAL(18,2), (100*CAST(DurationSec AS DECIMAL)) / CAST (avgDurationSec as DECIMAL)) END AS DurationRatio, 
 NextRunDate, 
 StepCommand, 
 HistoryMessage 
 FROM 
 ( 
 SELECT j.name JobName,j.enabled Enabled, 
 (select COUNT(1) from msdb..sysjobschedules jss where jss.job_id = j.job_id) ScheduleCount, 
 (select COUNT(1) from msdb..sysjobsteps jps where jps.job_id = j.job_id) StepCount, 
 ls1.job_history_id HistoryID, 
 ls1.start_execution_date StartDate, 
 ls1.stop_execution_date FinishDate, 
 ls1.last_executed_step_id LastStepID, 
 DATEDIFF(SECOND, ls1.start_execution_date, CASE WHEN ls1.stop_execution_date IS NULL THEN GETDATE() ELSE ls1.stop_execution_date END) DurationSec, 
 ISNULL(avgSec, 0) avgDurationSec, 
 ls1.next_scheduled_run_date NextRunDate, 
 st.step_name LastStep, st.command StepCommand, st.database_name DatabaseName, 
 h.message HistoryMessage, 
 CASE WHEN h.job_id IS NULL THEN 'Never Run' ELSE 
 CASE h.run_status 
 WHEN 0 THEN 'Failed' 
 WHEN 1 THEN 'Succeeded' 
 WHEN 2 THEN 'Retry' 
 WHEN 3 THEN 'Canceled' END END RunStatus, 
 h.run_date rawRunDate, 
 h.run_time rawRunTime, 
 h.run_duration rawRunDuration 
 FROM msdb..sysjobactivity ls1 (NOLOCK) 
 INNER JOIN msdb..sysjobs j (NOLOCK) ON ls1.job_id = j.job_id 
 INNER JOIN 
 ( 
 SELECT job_id JobID, MAX(session_id) LastSessionID 
 FROM msdb..sysjobactivity (NOLOCK) 
 GROUP BY job_id 
 ) ls2 ON ls1.job_id = ls2.JobID and ls1.session_id = ls2.LastSessionID 
 LEFT OUTER JOIN msdb..sysjobsteps st (NOLOCK) ON st.job_id = j.job_id and ls1.last_executed_step_id = st.step_id 
 LEFT OUTER JOIN msdb..sysjobhistory h (NOLOCK) ON h.instance_id = ls1.job_history_id 
 LEFT OUTER JOIN 
 ( 
 SELECT j.job_id JobID, SUM(h.avgSecs) avgSec 
 FROM msdb..sysjobs j (NOLOCK) 
 INNER JOIN 
 ( 
 SELECT job_id, step_id, AVG(run_duration/10000*3600 + run_duration%10000/100*60 + run_duration%100) avgSecs 
 FROM msdb..sysjobhistory 
 WHERE step_id > 0 AND run_status = 1 
 GROUP BY job_id,step_id 
 ) h on j.job_id = h.job_id 
 GROUP BY j.job_id 
 ) jobavg ON jobavg.JobID = j.job_id 
 )jj 
 WHERE (@ShowDisabled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR Enabled = 1) 
 AND (@JobName IS NULL OR JobName = @JobName) 
 AND (@ShowUnscheduled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR ScheduleCount > 0) 
 AND (@JobThresholdSec = 0 OR DurationSec >= @JobThresholdSec) 
 AND (@AvgExecThresholdSec = 0 OR avgDurationSec >= @AvgExecThresholdSec) 
)x 
ORDER BY CASE Info 
WHEN 'Running' THEN 0 
WHEN 'Failed' THEN 1 
WHEN 'Retry' THEN 2
WHEN 'Succeeded' THEN 3
WHEN 'Canceled' THEN 4
WHEN 'No steps' THEN 5 
WHEN 'Not scheduled' THEN 6
WHEN 'Disabled' THEN 7
WHEN 'Never Run' THEN 8
WHEN 'UNKNOWN' THEN -1
ELSE -2 END, NextRunDate, JobName

Rate

5 (10)

Share

Share

Rate

5 (10)