SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job Summary


Job Summary

Author
Message
adnan.korkmaz
adnan.korkmaz
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 167
Comments posted to this topic are about the item Job Summary
bslchennai
bslchennai
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 97
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.
adnan.korkmaz
adnan.korkmaz
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 167
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.
bslchennai
bslchennai
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 97
Thanks dear it is working now.
adnan.korkmaz
adnan.korkmaz
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 167
You are welcome. Hope you find it useful.
kwitzell
kwitzell
SSChasing Mays
SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)

Group: General Forum Members
Points: 650 Visits: 754
Excellent Script! Thanks!



logicinside22
logicinside22
SSC Eights!
SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)

Group: General Forum Members
Points: 990 Visits: 1408
nice script ,
can we so setup like the result of this procedure we get in mail with excel format file?

Aim to inspire rather than to teach.
SQL Server DBA
adnan.korkmaz
adnan.korkmaz
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 167
I am sure you can set it up, while I really don't have the time. :-)
Dave62
Dave62
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3354 Visits: 2796
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!
Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16198 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search