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


Alert Procedure for Long-Running Job


Alert Procedure for Long-Running Job

Author
Message
Janine Bocciardi
Janine Bocciardi
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 30
Comments posted to this topic are about the item Alert Procedure for Long-Running Job



dweil
dweil
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 3324
Can this script be modified so that it shows all jobs that are running / executing over a certain threshold rather then a specific job.

thanks

David Weil



lisa.randles
lisa.randles
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 500
FYI~

When I ran this on 2008 R2, I received the error:

Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1 SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

So I changed the stored proc to use Database Mail as opposed to SQLMail by editing the following, in order:

Changed @message to @body
Changed all references to @message_text to @message
Changed EXEC master..xp_sendmail to EXEC msdb..sp_send_dbmail

I feel pretty smart, as I'm a complete and total newb! :-D

<textarea rows="20" wrap="off" class="code">
CREATE proc sp_check_job_running
@job_name char(50),
@minutes_allowed int,
@person_to_notify varchar(50)
AS

DECLARE @var1 char(1),
@process_id char(8),
@job_id_char char(8),
@minutes_running int,
@message varchar(255)

select @job_id_char = substring(CAST(job_id AS char(50)),1,8)
from msdb..sysjobs
where name = @job_name

select @process_id = substring(@job_id_char,7,2) +
substring(@job_id_char,5,2) +
substring(@job_id_char,3,2) +
substring(@job_id_char,1,2)


select @minutes_running = DATEDIFF(minute,last_batch, getdate())
from master..sysprocesses
where program_name LIKE ('%0x' + @process_id +'%')

if @minutes_running > @minutes_allowed
BEGIN
select @message = ('Job '
+ UPPER(SUBSTRING(@job_name,1,LEN(@job_name)))
+ ' has been running for '
+ SUBSTRING(CAST(@minutes_running AS char(5)),1,LEN(CAST(@minutes_running AS char(5))))
+ ' minutes, which is over the allowed run time of '
+ SUBSTRING(CAST(@minutes_allowed AS char(5)),1,LEN(CAST(@minutes_allowed AS char(5))))
+ ' minutes.')
EXEC msdb..sp_send_dbmail
@recipients = @person_to_notify,
@body = @message,
@subject = 'Long-Running Job to Check'
END

</textarea>
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