Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Alert Procedure for Long-Running Job Expand / Collapse
Posted Sunday, September 30, 2007 2:34 AM


Group: General Forum Members
Last Login: Thursday, September 10, 2015 3:12 PM
Points: 19, Visits: 30
Comments posted to this topic are about the item Alert Procedure for Long-Running Job

Post #404627
Posted Tuesday, June 24, 2008 7:17 AM


Group: General Forum Members
Last Login: 2 days ago @ 10:32 AM
Points: 139, Visits: 3,101
Can this script be modified so that it shows all jobs that are running / executing over a certain threshold rather then a specific job.


David Weil

Post #522524
Posted Monday, September 24, 2012 2:07 PM

Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 14, 2016 12:28 PM
Points: 54, Visits: 500

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!

<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)

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) +

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

if @minutes_running > @minutes_allowed
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'

Post #1363712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse