Sample Code: Run a SQL Server Agent job only if it will succeed

• Nadrek

  SSC-Insane

  Points: 20039

  More actions

  June 23, 2014 at 12:36 pm

  #146889

  Business case: Somewhat similarly to The 2011 post A Saturday QOTD - If you get it right and live in Nashville, TN a few Happy Hour Brews are on ME!, I have a need to have a job run from another job without raising an error if it's already running or queued.

  After some research, I've come up with the following IF EXISTS; if anyone would like to review it and comment on improvements (or use it), I would appreciate it.

  First, a test agent job that waits 2 seconds.

  USE [msdb]

  GO

  BEGIN TRANSACTION

  DECLARE @ReturnCode INT

  SELECT @ReturnCode = 0

  IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

  BEGIN

  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  END

  DECLARE @jobId BINARY(16)

  EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TEST WAIT 2 SECONDS',

  @enabled=1,

  @notify_level_eventlog=0,

  @notify_level_email=0,

  @notify_level_netsend=0,

  @notify_level_page=0,

  @delete_level=0,

  @description=N'No description available.',

  @category_name=N'[Uncategorized (Local)]',

  @owner_login_name=N'sa', @job_id = @jobId OUTPUT

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'wait 2 seconds',

  @step_id=1,

  @cmdexec_success_code=0,

  @on_success_action=1,

  @on_success_step_id=0,

  @on_fail_action=2,

  @on_fail_step_id=0,

  @retry_attempts=0,

  @retry_interval=0,

  @os_run_priority=0, @subsystem=N'TSQL',

  @command=N'WAITFOR DELAY ''00:00:02''',

  @database_name=N'master',

  @flags=0

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  COMMIT TRANSACTION

  GOTO EndSave

  QuitWithRollback:

  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

  EndSave:

  GO

  Now the actual IF EXISTS, implemented in quadruplicate with timed delays as a test harness:

  -- Check time

  SELECT CURRENT_TIMESTAMP

  -- First we run it

  IF EXISTS (

  SELECT sj.job_id

  FROM msdb.dbo.sysjobs sj

  LEFT OUTER JOIN

  (

  SELECT sa.job_id

  FROM msdb.dbo.sysjobactivity sa

  -- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx

  -- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'

  INNER JOIN

  (

  SELECT MAX(session_ID) AS MaxSession_ID

  FROM msdb.dbo.syssessions

  ) currentSessionIErestart

  ON currentSessionIErestart.MaxSession_ID = sa.session_id

  WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL

  ) dvIsRunningNow

  ON dvIsRunningNow.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sj.name = 'TEST WAIT 2 SECONDS'

  AND dvIsRunningNow.job_id IS NULL

  )

  EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'

  -- Now the first test, another run very quickly afterwards to check for the "job already has a pending request" issue (sa.start_execution_date null)

  IF EXISTS (

  SELECT sj.job_id

  FROM msdb.dbo.sysjobs sj

  LEFT OUTER JOIN

  (

  SELECT sa.job_id

  FROM msdb.dbo.sysjobactivity sa

  -- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx

  -- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'

  INNER JOIN

  (

  SELECT MAX(session_ID) AS MaxSession_ID

  FROM msdb.dbo.syssessions

  ) currentSessionIErestart

  ON currentSessionIErestart.MaxSession_ID = sa.session_id

  WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL

  ) dvIsRunningNow

  ON dvIsRunningNow.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sj.name = 'TEST WAIT 2 SECONDS'

  AND dvIsRunningNow.job_id IS NULL

  )

  EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'

  -- And now a SELECT to see what weirdness may exist if we get an error

  SELECT CURRENT_TIMESTAMP, sj.job_id, sa.*

  FROM msdb.dbo.sysjobs sj

  INNER JOIN msdb.dbo.sysjobactivity sa

  ON sa.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sa.job_history_id IS null

  AND sj.name = 'TEST WAIT 2 SECONDS'

  -- Now the second test, another run after a second to check for the "job already running" issue (sa.start_execution_date not null)

  WAITFOR DELAY '00:00:01'

  IF EXISTS (

  SELECT sj.job_id

  FROM msdb.dbo.sysjobs sj

  LEFT OUTER JOIN

  (

  SELECT sa.job_id

  FROM msdb.dbo.sysjobactivity sa

  -- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx

  -- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'

  INNER JOIN

  (

  SELECT MAX(session_ID) AS MaxSession_ID

  FROM msdb.dbo.syssessions

  ) currentSessionIErestart

  ON currentSessionIErestart.MaxSession_ID = sa.session_id

  WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL

  ) dvIsRunningNow

  ON dvIsRunningNow.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sj.name = 'TEST WAIT 2 SECONDS'

  AND dvIsRunningNow.job_id IS NULL

  )

  EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'

  -- And now a SELECT to see what weirdness may exist if we get an error

  SELECT CURRENT_TIMESTAMP, sj.job_id, sa.*

  FROM msdb.dbo.sysjobs sj

  INNER JOIN msdb.dbo.sysjobactivity sa

  ON sa.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sa.job_history_id IS null

  AND sj.name = 'TEST WAIT 2 SECONDS'

  -- Now the third test, another run after just over another second to make sure it starts again.

  -- NOTE: A delay of even '00:00:01.8' failed to start the job most of the time.

  WAITFOR DELAY '00:00:02.3'

  IF EXISTS (

  SELECT sj.job_id

  FROM msdb.dbo.sysjobs sj

  LEFT OUTER JOIN

  (

  SELECT sa.job_id

  FROM msdb.dbo.sysjobactivity sa

  -- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx

  -- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'

  INNER JOIN

  (

  SELECT MAX(session_ID) AS MaxSession_ID

  FROM msdb.dbo.syssessions

  ) currentSessionIErestart

  ON currentSessionIErestart.MaxSession_ID = sa.session_id

  WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL

  ) dvIsRunningNow

  ON dvIsRunningNow.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sj.name = 'TEST WAIT 2 SECONDS'

  AND dvIsRunningNow.job_id IS NULL

  )

  EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'

  -- And now a SELECT to see what weirdness may exist if we get an error

  SELECT CURRENT_TIMESTAMP, sj.job_id, sa.*

  FROM msdb.dbo.sysjobs sj

  INNER JOIN msdb.dbo.sysjobactivity sa

  ON sa.job_id = sj.job_id

  WHERE sj.enabled = 1

  AND sa.job_history_id IS null

  AND sj.name = 'TEST WAIT 2 SECONDS'