Start SQL Job from Stored Procedure and wait until SQL Job Complete - TSQL

Problem

• The system stored procedure sp_start_job is a very useful T-SQL command that starts a SQL Agent job. The problem with this is that it starts the job and it doesn't wait until the job is finished and sometimes I want to start a job and wait until the job is finished and move on to another job or task that depends on the result of the previous job.

  For example, I want to make sure the Pre Snapshot job is finished before I move on to the next step. Another example, we are using Change Data Capture on replication subscribers and I want to break the Change Data Capture before I apply the snapshot and there is a job that takes care of that, so I want to run that job first and make sure the job has completed before I move on to next step.

Solution

• I have created a custom stored procedure called "sp_start_job_wait" and basically, it starts a job by using sp_start_job and checks if the job is finished using a loop.

  Let's take a look at a couple of examples.

  ---

  Example 1

  This is the typical use of sp_start_job_wait. This will run job "zzzDBATest" and then by default, it checks the job status every 5 seconds and exits based on the job completion status.

  -- Starts a job call zzzDBATest  
  exec sp_sp_start_job_wait 'zzzDBATest'

  When the job succeeded, the output looks like this.

  Job 'zzzDBATest' started successfully.  
  JOB IS RUNNING  
  JOB IS RUNNING  
  JOB IS RUNNING  
  The job ran Successful

  When the job failed, the output looks like this.

  Job 'zzzDBATest' started successfully.  
  Msg 50000, Level 16, State 1, Procedure sp_sp_start_job_wait, Line 76  [ERROR]:zzzDBATest job is either failed or not in good state. Please check

  When the job is cancelled, the output looks like this.

  Job 'zzzDBATest' started successfully.  
  JOB IS RUNNING  
  JOB IS RUNNING  
  The job is Cancelled

  ---

  Example 2

  You can also specify parameters like this if you know the job takes longer to finish and you don't want to check every 5 seconds. In this example, we are checking every 5 minutes.

  -- Starts a job call zzzDBATest

  DECLARE @RetStatus int  
  exec sp_sp_start_job_wait    
  @job_name = 'DBA - Test Job',   
  @WaitTime = '00:05:00',    
  @JobCompletionStatus = @RetStatus 
  OUTPUT  select @RetStatus  

  ---

  Code

  Here is the stored procedure and here are some points regarding the proc.

  • It uses the xp_sqlagent_enum_jobs store procedure to check the status of the job
  • I used the "raiserror('JOB IS RUNNING', 0, 1 ) WITH NOWAIT" to print out the status if it is running or not right away while I am running from SQL Server Management Studio. This way I can see if it is working or hung.
  • It queries the sysjobhistory table
  CREATE PROCEDURE dbo.sp_sp_start_job_wait
  (
  @job_name SYSNAME,
  @WaitTime DATETIME = '00:00:05', -- this is parameter for check frequency
  @JobCompletionStatus INT = null OUTPUT
  )
  AS
  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET NOCOUNT ON
  
  -- DECLARE @job_name sysname
  DECLARE @job_id UNIQUEIDENTIFIER
  DECLARE @job_owner sysname
  
  --Createing TEMP TABLE
  CREATE TABLE #xp_results (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)
  
  SELECT @job_id = job_id FROM msdb.dbo.sysjobs
  WHERE name = @job_name
  
  SELECT @job_owner = SUSER_SNAME()
  
  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
  
  -- Start the job if the job is not running
  IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
  EXEC msdb.dbo.sp_start_job @job_name = @job_name
  
  -- Give 2 sec for think time.
  WAITFOR DELAY '00:00:02'
  
  DELETE FROM #xp_results
  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
  
  WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
  BEGIN
  
  WAITFOR DELAY @WaitTime
  
  -- Information
  raiserror('JOB IS RUNNING', 0, 1 ) WITH NOWAIT
  
  DELETE FROM #xp_results
  
  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
  
  END
  
  SELECT top 1 @JobCompletionStatus = run_status
  FROM msdb.dbo.sysjobhistory
  WHERE job_id = @job_id
  and step_id = 0
  order by run_date desc, run_time desc
  
  IF @JobCompletionStatus = 1
  PRINT 'The job ran Successful'
  ELSE IF @JobCompletionStatus = 3
  PRINT 'The job is Cancelled'
  ELSE
  BEGIN
  RAISERROR ('[ERROR]:%s job is either failed or not in good state. Please check',16, 1, @job_name) WITH LOG
  END
  
  RETURN @JobCompletionStatus
  
  GO

  There you have it. Now, by using this proc I can control jobs and job dependencies easily. By using this process, I was able to make post-snapshot tasks a dependency with the replication script.

  I'd like to thank Srikant Tadimalla and Sourav Biswas, you can find a detailed blog by visiting this link

Next Steps

You can change the stored proc to have additional parameters like sp_start_job does by adding @job_id, @step_name, etc...

You can change the stored proc to call SQL Server remotely by putting the @remote_server_name parameter and either using xp_cmdshell with sqlcmd or OPENQUERY

 

HAPPY CODING