Job Summary

  • Comments posted to this topic are about the item Job Summary

  • Hi adnan,

    While compiling your code i am getting the errors like below.

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 10

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 31

    Incorrect syntax near 'ScheduleCount'.

    Msg 102, Level 15, State 1, Line 32

    Incorrect syntax near 'StepCount'.

    Msg 102, Level 15, State 1, Line 64

    Incorrect syntax near '?'.

    please advise me to modify this.

  • Hi there,

    It seems the post page on SQL Server Central replaces certain characters(space / tab) with others, due to a bug I assume.

    I have checked the characters on the error lines that appear to be spaces (ascii 32), were actually replaced by ascii 63.

    So try this one:

    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

    Hope that helps.

  • Thanks dear it is working now.

  • You are welcome. Hope you find it useful.

  • Excellent Script! Thanks!

  • nice script ,

    can we so setup like the result of this procedure we get in mail with excel format file?

  • I am sure you can set it up, while I really don't have the time. 🙂

  • Thanks for the great script!!!

    I gave it 5 stars.

    Another work-around for the problem with copy and paste from the article is to paste to Notepad first. This removes all the unwanted special charcters. Then copy from Notepad to SSMS and it will run fine.

    Enjoy!

  • Thanks for the script.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply