how to Automatically stop a Job or to be warned when duration is higher than xx hours ?

• guennal

  SSC Enthusiast

  Points: 161

  More actions

  March 26, 2009 at 11:44 am

  #202288

  Hello,

  I would like to know if there is a way to be warned or to directly stop a running job (ssis packages or backup for instance) automatically after it is started more than xx hours ?

  Thanks for your answers

  regards,

  Guennal

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  March 27, 2009 at 7:51 am

  #967440

  I have script that I use shows job state. So you could do something like this: If job_state = 1, then stop the job using sp_stop_job (http://msdn.microsoft.com/en-us/library/ms182793.aspx)

  DECLARE @jn NVARCHAR(128)

  SET @jn = 'jobname' --comment out to show all jobs

  DECLARE @xp_results TABLE (job_id

  UNIQUEIDENTIFIER NOT NULL,

  last_run_date INT NOT NULL,

  last_run_time INT NOT NULL,

  next_run_date INT NOT NULL,

  next_run_time INT NOT NULL,

  next_run_schedule_id INT NOT NULL,

  requested_to_run INT NOT NULL, -- BOOL

  request_source INT NOT NULL,

  request_source_id sysname COLLATE database_default NULL,

  running INT NOT NULL, -- BOOL

  current_step INT NOT NULL,

  current_retry_attempt INT NOT NULL,

  job_state INT NOT NULL)

  INSERT @xp_results

  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

  SELECT j.[name], r.job_state

  FROM @xp_results r

  INNER JOIN msdb.dbo.sysjobs j on r.job_id = j.job_id

  WHERE (j.[name] = @jn or @jn is NULL)

  -- JOB STATE:

  -- 0 = Not idle or suspended

  -- 1 = Executing

  -- 2 = Waiting For Thread

  -- 3 = Between Retries

  -- 4 = Idle

  -- 5 = Suspended

  -- 6 = WaitingForStepToFinish

  -- 7 = PerformingCompletionActions