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

  • 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?

  • 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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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 4 years, 4 months ago by  krypto69.
  • 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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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