Agent Job After Another Job: Start the Next Job on Success

To run an Agent job after another job, end the first job with a sp_start_job step for the second. There is no guessing about run times and no polling.

The Problem With Schedules

A client wanted a SQL Server Agent job to start when another job finished. The usual first idea is a schedule. Start the second job at 2:00 AM because the first one takes about an hour. That works until the first job takes two hours, and then both run at once. The other idea is a job that watches the history of the first one. That is a lot of code for a simple wish.

The simple answer is a step. Agent can start any job from a T-SQL step with the msdb procedure sp_start_job. Put that step last in the first job, and the second job starts when the first has done its work.

Build the Pair

The demo creates two jobs with the documented msdb procedures, and it never writes to a system table. The first job, ChainDemo Load, has two steps. Step 1 loads data and goes on to the next step when it succeeds. Step 2 starts the second job and ends the job. The second job, ChainDemo Report, has one step. Both steps only run SELECT 1, because the demo is about the chain.

The demo creates two jobs in msdb and removes them at the end. Run it on a test instance, and run the cleanup if you stop early.

USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name = N'ChainDemo Load') EXEC dbo.sp_delete_job @job_name = N'ChainDemo Load';
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name = N'ChainDemo Report') EXEC dbo.sp_delete_job @job_name = N'ChainDemo Report';
EXEC dbo.sp_add_job @job_name = N'ChainDemo Report';
EXEC dbo.sp_add_jobstep @job_name = N'ChainDemo Report', @step_name = N'Build report', @subsystem = N'TSQL', @database_name = N'master', @command = N'SELECT 1;';
EXEC dbo.sp_add_jobserver @job_name = N'ChainDemo Report', @server_name = N'(local)';
EXEC dbo.sp_add_job @job_name = N'ChainDemo Load';
EXEC dbo.sp_add_jobstep @job_name = N'ChainDemo Load', @step_name = N'Load data', @step_id = 1, @subsystem = N'TSQL', @database_name = N'master', @command = N'SELECT 1;', @on_success_action = 3;
EXEC dbo.sp_add_jobstep @job_name = N'ChainDemo Load', @step_name = N'Start report job', @step_id = 2, @subsystem = N'TSQL', @database_name = N'msdb', @command = N'EXEC dbo.sp_start_job @job_name = N''ChainDemo Report'';', @on_success_action = 1, @on_fail_action = 2;
EXEC dbo.sp_add_jobserver @job_name = N'ChainDemo Load', @server_name = N'(local)';

The Agent service on the test server is stopped. SQL Server prints a note that Agent cannot be told about the change. The jobs exist anyway, and the demo never runs them. Read the steps back to see the chain.

SELECT j.name AS JobName, s.step_id AS StepId, s.step_name AS StepName, s.command AS Command, s.on_success_action AS OnSuccess, s.on_fail_action AS OnFail
FROM dbo.sysjobs AS j
JOIN dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE j.name LIKE N'ChainDemo%'
ORDER BY j.name DESC, s.step_id;

The two action columns use numeric codes. A 1 means quit with success. A 2 means quit with failure. A 3 means go to the next step. Step 1 of the load job goes to step 2. Step 2 quits with success, and a failure in either step ends the job with failure. The report job starts only after the load step has succeeded.

What sp_start_job Waits For

The procedure sends a request to Agent and returns. It does not wait for the second job to finish. The step that calls it succeeds as soon as Agent accepts the request. If the report job fails an hour later, the load job still shows success. Read the history of both jobs, and do not expect one job to report the other.

Put the call in the last step on purpose. Then the first job has no work left when the second begins. That matters when the second job reads what the first one writes. A second job that starts while the first still holds locks can block on them.

When the Second Job Is Already Running

The documentation says a job cannot start while it is already running. If the second job is still running when the call arrives, Agent refuses the request. The calling step then fails. The load job then ends with failure, although its own work was fine. Decide what you want in that case. A failed first job that alerts someone is a good answer. A refused call that nobody reads is not.

Who Can Start a Job

A member of sysadmin can start any job. A member of the SQLAgentOperatorRole role in msdb can start any local job as well. A member of SQLAgentUserRole can start only jobs the member owns. A job step runs as the job owner. The owner of the first job needs one of these rights for the second job.

Two Steps in One Job

An Agent job after another job is the right tool when the jobs have different owners, schedules or alerts. When one person owns both pieces of work, use one job with two steps instead. The first step goes to the second on success, as step 1 does in the demo. The history stays in one place, and a failure stops the job without a second job to check.

You could argue that a chain hides the order of work inside step text, where nobody looks. That is true. Name the steps well. Put the next job in the name of the last step, as the demo does with Start report job.

Jobs on Different Servers

A step runs on the server that owns the job. Starting a job on another server needs a step that calls sp_start_job there. A linked server that allows remote procedure calls is one way to do it. The step calls the procedure by its four-part name, and RPC Out must be on for the linked server.

EXEC [RemoteServer].msdb.dbo.sp_start_job @job_name = N'Report job';

This call follows the documented behavior, and the demo did not run it. The order does not change. The second job starts only when the first job reaches its last step.

What to Remember

To run an Agent job after another job, put the sp_start_job call in the last step of the first job. Test the case where the second job is busy. Check both histories, because the call does not report the outcome. Remove the demo jobs when you finish.

USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name = N'ChainDemo Load') EXEC dbo.sp_delete_job @job_name = N'ChainDemo Load';
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name = N'ChainDemo Report') EXEC dbo.sp_delete_job @job_name = N'ChainDemo Report';

A schedule is not a dependency, it is a guess that the first job is done.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.