Send me an email if this job run longer than 30 minutes

  • krypto69

    SSChampion

    Points: 13506

    Hi

    I have a job that I need to be notified/emailed if it runs longer than 30 minutes.

    What is the best way to do that?

  • Mr. Brian Gale

    SSC-Insane

    Points: 23174

    Not sure if it is the best way, but I just set up a second job that starts at the same time as the one I wanted to monitor and it does a wait for 30 minutes and after the 30 minute window, it checks to see if the monitored job is still running.  if so, send an email, otherwise complete and run again the next time the first job is being run.

  • ScottPletcher

    SSC Guru

    Points: 98569

    Have the job you want to monitor immediately start a second job that will notify you if the run time is exceeded.  You could even have the main job pass a different test time interval (if for whatever reason you want something other than 30 mins for that run) and/or a different list of people notify if the job runs too long (someone's on vacation, etc.) to the second job if you ever needed to.

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • krypto69

    SSChampion

    Points: 13506

    Okay gotcha..

     

    This is my work in progress....I was hoping to create the below in the step before the job starts. But it isn't working...

    Declare @time as int
    SELECT @time=DATEDIFF(MINUTE,aj.start_execution_date,GetDate())
    FROM msdb..sysjobactivity aj
    JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
    WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
    AND aj.start_execution_date IS NOT NULL -- job is currently running
    AND sj.name = 'Automated Claim Closing'
    and not exists( -- make sure this is the most recent run
    select 1
    from msdb..sysjobactivity new
    where new.job_id = aj.job_id
    and new.start_execution_date > aj.start_execution_date
    )
    If @time>1 BEGIN

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = '123@a.com',
    @body = 'The Automated Job is running long ',
    @subject = 'The Automated Job is running long ' ;

    END

    • This reply was modified 1 month, 2 weeks ago by  krypto69.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23174

    If you are going with Scott's approach, step 1 of the job would be to start the job you want to monitor.  Next, you wait for some period of time (30 minutes in your case), and then you look to see if the job is running.

    Using what you had BEFORE the job starts will give you NULL as @time which will return FALSE for your IF statement and thus do nothing as the job is not running.

    My steps are similar to Scott's listed above of:

    1 - start the job you want to monitor

    2 - wait for your self-set timeout

    3 - send alert if job is still running

    Scott's approach is a lot more modular having a stored procedure handle steps 2 and 3 and allows for code reuse, so I do like their approach better than mine.  My approach ended up having 2 jobs that do work and 2 jobs that watch for the specific long running job.  I also like Scott's approach of having a the monitor job start up the work job.  my approach breaks if someone changes a schedule.

  • krypto69

    SSChampion

    Points: 13506

    Oh I see....now. Thanks! Yeah I like it also.

  • ScottPletcher

    SSC Guru

    Points: 98569

    Not exactly.

    The first step in the main job is to start another job that will check on the first job, like this:

    EXEC msdb..sp_start_job 'Automated Claim Closing Monitor'

    The main job then goes on claim closing or whatever processing it needs to do.

    The first step of the monitor job is to WAIT DELAY for whatever time is needed, then check if the job is still executing, something like this:

    --Step_1

    WAITFOR DELAY '00:30:00' --step_1

    --Step_2

    DECLARE @body nvarchar(max)
    DECLARE @job_name nvarchar(128)
    DECLARE @job_exec_mins int
    DECLARE @recipients nvarchar(max)
    DECLARE @subject nvarchar(255)

    SET @job_name = 'Automated Claim Closing'
    SET @recipients = '123@a.com'

    SELECT @job_exec_mins=CEILING(DATEDIFF(SECOND,aj.start_execution_date,GetDate()) / 60.0)
    FROM msdb..sysjobactivity aj
    JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
    WHERE aj.session_id = (SELECT MAX(session_id) FROM msdb..syssessions) -- make sure this is a current run
    AND sj.name = @job_name
    AND aj.start_execution_date IS NOT NULL -- job is currently running
    AND aj.stop_execution_date IS NULL -- job hasn''t stopped running

    If @job_exec_mins>1 BEGIN
    SET @subject = 'The Automated Job is running long, now for ' + CAST(@job_exec_mins AS varchar(5)) + ' minutes.'
    SET @body = 'The Automated Job is running long, now for ' + CAST(@job_exec_mins AS varchar(5)) + ' minutes.'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = @recipients,
    @body = @body,
    @subject = @subject

    END /*IF*/

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher

    SSC Guru

    Points: 98569

    As I noted earlier, you could also pass values to the monitoring job to control the check time interval and/or the options for how many times to check on the job and/or whether or not to kill the orig job at a certain point, etc..

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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