Alert Procedure for Long-Running Job

Question

Alert Procedure for Long-Running Job

Janine Bocciardi

SSC Veteran

Points: 277
More actions
September 30, 2007 at 2:34 am

#175385

Comments posted to this topic are about the item Alert Procedure for Long-Running Job

Viewing 3 posts - 1 through 2 (of 2 total)

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

dweil SSCrazy Points: 2408 More actions · Answer 1

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 Default port Points: 1448 More actions · Answer 2

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! 😀

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>