Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 7:26 AM
Points: 18, Visits: 27
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:11 AM
Points: 129, Visits: 2,437
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



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: Thursday, October 16, 2014 12:15 PM
Points: 52, Visits: 452
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_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>
Post #1363712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse