How to start SQL Server job using TSQL and wait for it to complete?

If you ever had a need to start a SQL job using sp_start_job stored procedure in msdb then you know once the job is started successfully, it returns the control back to the user immediately and does not wait for the job to complete.

But what if you want your script to wait for the job to complete and then do some other stuff?

Here is a script I have written a while back that you can use to start any SQL job job. The script will wait for the job to complete and returns job completion code back to you.

I actually use this as a stored procedure (uploaded at GitHub) but for the demo purpose I am including it here in a script format. 

Download the SP from GitHub

Download SQL Query From GitHub

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE
@job_name SYSNAME = 'test',     -- INPUT YOUR JOB NAME HERE
@WaitTime DATETIME = '00:00:05',  -- default check frequency
@JobCompletionStatus INT


-- CHECK IF IT IS A VALID AND EXISTING JOB NAME

IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name = @job_name)
BEGIN
       RAISERROR ('[ERROR]:[%s] job does not exist. Please check',16, 1, @job_name) WITH LOG
       RETURN
END


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 only if it is not already running
IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
       EXEC msdb.dbo.sp_start_job @job_name = @job_name

-- Wait x seconds to ensure the job is startable and has indeed started

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

       -- Display informational message at each interval
       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

Caveats: While there are few caveats I can think of, there are couple that I think are more obvious and worth mentioning here:

• It uses the undocumented extended stored procedure so all the caveats that applies to any such object also applies to here.

• While it does some error checking and handling, many others are like encapsulating it inside a TRY/CATCH etc. are skipped.

• If the value for parameter @WaitTime is excessive relative to the typical duration of the specified job, it may introduce inefficiencies. For example, if @WaitTime is set to '01:00:00' (checking every hour) but the job typically completes within a few seconds, this stored procedure will still wait for the full hour before checking if the job has completed. This approach can lead to some potential issues:

• Unnecessary delays in workflow progression: The procedure may wait much longer than needed before moving to the next step in a workflow.
• Missed status changes: If the job completes and then runs again within the @WaitTime interval, the procedure might miss intermediate status changes.
• Resource inefficiency: Excessively long wait times can tie up resources and reduce overall system responsiveness.

To mitigate these issues, it's advisable to set @WaitTime to a value that balances the need for frequent checks against the expected duration of the job.