Alert Procedure for Long-Running Job

  • Janine Bocciardi

    SSC Veteran

    Points: 277

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

  • dweil

    SSCrazy

    Points: 2408

    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

    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>

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

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