sp_start_job starts a SQL Server Agent job and returns before the job finishes. A script that starts a load and immediately runs a report can read half-loaded data. To wait for an Agent job to finish, track the run you requested, poll its activity, and check the final outcome.
Decide What It Means for an Agent Job to Finish
The simplest solution can be a single Agent job with ordered steps and success or failure actions. Use a separate polling script when jobs have different owners, schedules, or dependencies that cannot be combined. Decide what it means for the Agent job to finish: the job has stopped, the final step succeeded, and the expected data is ready. Those are related but distinct checks.
I have seen a job report success while a data-quality step was missing from its definition. The waiting script correctly saw success, but the report was still wrong. What table or marker proves the load is complete? Add that postcondition after the Agent outcome check.
Identify the Job and Agent Session
Resolve the job name to job_id in msdb.dbo.sysjobs and read the latest Agent session ID from msdb.dbo.syssessions. sysjobactivity has one row per job for that Agent session, with start and stop execution dates and a job-history reference. Capture the request time before calling sp_start_job so a previous run is not mistaken for the new one.
USE msdb;
GO
DECLARE @job_name sysname = N'Nightly Sales Load';
SELECT j.job_id, a.session_id,
a.start_execution_date, a.stop_execution_date,
a.job_history_id
FROM dbo.sysjobs AS j
JOIN dbo.sysjobactivity AS a ON a.job_id = j.job_id
WHERE j.name = @job_name
AND a.session_id =
(SELECT MAX(session_id) FROM dbo.syssessions);The row can contain an older completed run before the new start is recorded. That is why the complete script checks start_execution_date >= @requested_at. If Agent restarts while you wait, the session changes; treat it as an interrupted observation and investigate rather than reporting success.
Poll With a Bound for the Agent Job to Finish
The loop below waits five seconds between checks and stops after ten minutes. It keeps a @finished flag, uses BREAK on completion and timeout, and checks the current Agent session. sp_start_job can fail if the job is disabled or already running, or if Agent is stopped. It then returns 1, and the script stops with its own start error. Without that check, my test waited out the full deadline and reported a timeout for a job that never started.
USE msdb;
GO
DECLARE @job_name sysname = N'Nightly Sales Load';
DECLARE @job_id uniqueidentifier =
(SELECT job_id FROM dbo.sysjobs WHERE name = @job_name);
DECLARE @agent_session int =
(SELECT MAX(session_id) FROM dbo.syssessions);
DECLARE @requested_at datetime = GETDATE();
DECLARE @deadline datetime2(0) = DATEADD(minute,10,SYSDATETIME());
DECLARE @finished bit = 0, @history_id int = NULL, @rc int;
IF @job_id IS NULL THROW 50001, 'Agent job not found.', 1;
EXEC @rc = dbo.sp_start_job @job_id = @job_id;
IF @rc <> 0 THROW 50005, 'Agent job did not start.', 1;
WHILE 1 = 1
BEGIN
SELECT @finished = CASE WHEN start_execution_date >= @requested_at
AND stop_execution_date IS NOT NULL
THEN 1 ELSE 0 END,
@history_id = CASE WHEN start_execution_date >= @requested_at
AND stop_execution_date IS NOT NULL
THEN job_history_id ELSE NULL END
FROM dbo.sysjobactivity
WHERE job_id = @job_id AND session_id = @agent_session;
IF @finished = 1 BREAK;
IF SYSDATETIME() >= @deadline BREAK;
WAITFOR DELAY '00:00:05';
END;
IF @finished = 0
THROW 50002, 'Agent job did not finish before timeout.', 1;
DECLARE @outcome int =
(SELECT run_status FROM dbo.sysjobhistory
WHERE instance_id = @history_id AND step_id = 0);
IF @outcome IS NULL
THROW 50003, 'Job stopped but outcome is not available.', 1;
IF @outcome <> 1
THROW 50004, 'Agent job completed without success.', 1;
SELECT N'SUCCESS' AS job_outcome, @history_id AS history_id;Run this in a controlled test with a short job first. run_status = 1 is success. SQL Agent history can be written just after the activity row changes; a production script can briefly retry the history lookup before declaring it unavailable. Keep that retry bounded and logged.
Handle a Timeout While Waiting for the Agent Job to Finish
A timeout does not stop the Agent job. It means the waiting script gave up. The Agent job can still be running and can finish later. Do not start a second load automatically after a timeout. Record job ID, Agent session, request time, current activity, and the data postcondition. Alert the owner and decide whether to continue waiting, cancel the job, or repair a stalled step.
A five-second poll is gentle for one job; hundreds of waiting scripts can burden msdb. Use a sensible interval and a deadline based on the expected runtime plus response time. The deadline should be long enough for legitimate slow days and short enough to keep downstream work from silently waiting forever.
Read History and Validate Data
sysjobhistory step_id 0 is the job outcome row. A success status there does not guarantee that all expected rows arrived if the job's own logic treats some failures as success. Verify a batch ID, completion marker, row count, or reconciliation result in the target database before opening the report gate. Keep that check tied to the specific run, not merely to today's date.
I save the history instance ID and the loaded batch ID in the pipeline log. When someone asks why the report was late, the timeline shows when the request started, when Agent finished, and when validation passed. If the load failed, the waiting script raises an error and the report does not run against partial data.
Distinguish the Run You Started
Job names identify definitions, not executions. A recurring job can have a completed activity row from an earlier run in the current Agent session. The request timestamp and start date prevent that row from satisfying the wait. A long-lived job can also be started by another scheduler at almost the same time, and sp_start_job can reject the second request. Handle that rejection explicitly rather than claiming to own the already running job.
For a critical pipeline, add a batch ID passed through a control table. The load writes that ID and a completion state; the report waits for the matching ID. That business marker remains useful even if Agent restarts, history is purged, or a job has several steps. The Agent state tells you whether execution ended; the batch marker tells you what data that execution delivered.
Avoid a False Success From History
The activity row's job_history_id links to a history record, but history writing can lag briefly. Poll the identified outcome for a few bounded retries. Do not select TOP 1 ORDER BY run_date DESC without tying it to the requested run. Two runs on the same day can make that query return the wrong outcome. Record the history ID and job ID in the pipeline log.
A status of failed, canceled, or retry is not success. Check the step messages for the root error and alert the owner with the precise run. If the Agent service restarted mid-wait, restart the observation from an authoritative batch marker or stop with an unknown-state error. Treat unknown as a reason to investigate, not as permission to run the report.
Keep the Poller Operational
A waiting T-SQL session consumes a connection while it sleeps. A handful of short waits is usually manageable; a large scheduler should use event-driven orchestration or Agent step dependencies. Set the timeout based on measured job duration, not a round number chosen under pressure. Add enough margin for backups or known maintenance that share resources.
I test four cases: normal success, job failure, timeout with the job still running, and an Agent restart. The downstream report should run only in the first case after its data check passes. That small matrix catches the common mistake of treating sp_start_job returning zero as proof that the load completed.
Related reading on this blog: Running SQL Agent Job After Completing Another Job and T-SQL Script to Check SQL Server Job History.
A finished Agent job is not a valid handoff by itself, it is valid after outcome and data checks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
