September 30, 2007 at 2:34 am
Comments posted to this topic are about the item Alert Procedure for Long-Running Job
June 24, 2008 at 7:17 am
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
September 24, 2012 at 2:07 pm
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! 😀
<textarea rows="20" wrap="off" class="code">
CREATE proc sp_check_job_running
@job_name char(50),
@minutes_allowedint,
@person_to_notifyvarchar(50)
AS
DECLARE @var1 char(1),
@process_idchar(8),
@job_id_charchar(8),
@minutes_running int,
@messagevarchar(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>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply