Setting Dependencies Between SQLServer Agent Jobs
Business Case
There are many occasions where we require scheduled processing within SQLServer. The easiest way to implement automated processing is by using scheduled jobs in SQLServer Agent. The vast majority of the cases required some kind of dependency being set among the jobs to control the sequence of execution.
A typical scenario for this kind of requirement is in the case of data warehouse processing jobs. In a typical data warehouse setup there will be multiple set of jobs for processing various layers like Staging, ODS, dimensional etc. In such cases dependency has to be implemented amongst the jobs to ensure that each layer processing starts only after the previous layer processing is completed.
This article explains two approaches you can use for setting the dependency between SQLAgent jobs.
Explanation
Consider the below scenario
Suppose we have three Jobs Job1,Job2 and Job3. The schedules for these three jobs are at say 9 AM, 2 PM and 6 PM respectively. The requirement is to make sure Job 2 processing starts only once Job 1 is completed. Similarly Job 3 should start processing only after Job 2 has finished.There are multiple approaches that can be applied for the above requirement based on the way the jobs are setup
Scenario 1: Each job having a defined schedule
In this case jobs are already having a defined schedule. So what we need to ensure is that the previous job has finished running before the current job starts running. This can be ensured using the following steps Add a step before actual starting step of the job which will check if the previous job has finished executing. Based on the outcome of the previous job the current job processing will be started. In case the previous job has not run to success for the day the execution of the current job will also get aborted indicating a failure. In case its still running current job execution will be suspended till its finished. This logic can be implemented in two ways
In case you've a audit and logging framework available you can utilize the audit control table available for this. On the minimum this table would have start and end date values for the job, the job run status and the fields to store inserted/modified record counts. The logic for this would look like the below
IF NOT EXISTS (
SELECT 1
FROM AuditTable
WHERE StartDate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND StartDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
AND RunStatus = 'Success'
AND JobName = 'Job1'
)
RAISERROR ('Previous job not yet succesfully completed',16,1)
In case there's no audit control table available you can make use of the standard sysjobactivity table in msdb database for the condition evaluation and logic would look like this
IF EXISTS (SELECT 1
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j
on j.job_id = ja.job_id
INNER JOIN (SELECT MAX(session_id) AS session_id
FROM msdb.dbo.syssessions) s
On s.session_id = ja.session_id
WHERE ja.start_execution_date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND ja.stop_execution_date IS NULL
AND j.name = 'Job1'
)
RAISERROR ('Previous job not yet successfully completed',16,1)
Once this logic is implemented next step is to make sure the retry settings are set for the job. This is required to make sure job execution is retried after a defined interval to see if previous job has finished by then
This can be done as below
Once this is done the job will try to rerun itself after the specified interval for the set frequency. You can set the frequency and interval based on threshold time up to which you expect the previous job might take to complete in the worst case.
ie suppose if Job1 starts at 3PM and Job2 starts at 6 PM and you expect Job1 to take upto 7:30 PM in the worst case you can set retry attempts for Job1 as 5 with retry interval of 18 minutes ie it will retry for 18 * 5 = 90 minutes before it stops finally indicating failure.
Scenario 1: Illustration
As an example try creating two jobs using the below script.
USE [msdb]
GO
/****** Object: Job [Job1] Script Date: 12/26/2014 19:01:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/26/2014 19:01:43 ******/
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'Job1',
@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'<LoginName>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step 1] Script Date: 12/26/2014 19:01:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1',
@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'DECLARE @dt time= GETDATE()
WHILE (CAST(GETDATE() AS time) <= DATEADD(minute,20,@dt))
BEGIN
WAITFOR DELAY ''00:00:05''
SELECT GETDATE()
END
SELECT ''Loop broken''',
@database_name=N'TestAccess',
@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
/****** Object: Job [Job2] Script Date: 12/26/2014 19:01:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/26/2014 19:01:43 ******/
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'Job2',
@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'<LoginName>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check if prev job is success] Script Date: 12/26/2014 19:01:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check if prev job is success',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=5,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF EXISTS (SELECT 1
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j
on j.job_id = ja.job_id
INNER JOIN (SELECT MAX(session_id) AS session_id
FROM msdb.dbo.syssessions) s
On s.session_id = ja.session_id
WHERE ja.start_execution_date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND ja.stop_execution_date IS NULL
AND j.name = ''Job1''
)
RAISERROR (''Previous job not yet succesfully completed'',16,1)
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Dummy Step] Script Date: 12/26/2014 19:01:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dummy Step',
@step_id=2,
@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'SELECT 1',
@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
Make sure you replace placeholder <LoginName> with the actual login details in your case.
This will create two jobs Job 1 and Job 2. Job 1 has a step which will be waiting in a loop until 20 minutes. Job 2 will have a step which will check for Job1 completion status before it starts. I've set a retry interval as 5 minutes for Job 2 and retry attempts as 5.
Here second job depends upon the completion of the first job for execution. Start the jobs simultaneously. The logic implemented in first job will make sure it runs for about 20 mins. Second jobs wait step will cause it to wait until first job is finished. So it will keep on retrying until first job gets over. This can be seen from the job history for Job2 which will look like the below
If you analyze the above figure you can clearly see multiple retry attempts by Job 2 whilst its wait for the Job 1 completion. Check the message which clearly indicates previous job was not completed. We have set this message in the T-SQL code above using RAISERROR to give more clarity to the log viewer.
Scenario 2: Only the first job or none of the jobs having a defined schedule
In case only first job has a set schedule or none of the job have a schedule we can go for an alternate approach which is much easier to implement.
In this case we will include an additional step as the last step in all the jobs except the last one. The step will have a statement to invoke the next job in the dependent list. This can be implemented using the system storedprocedure sp_start_job.
So the last step will look like this for the first job.
Once this is setup it will make sure the next job in the sequence is called by the last step of the previous job. So all the jobs will get executed one after the other without any waiting period.
Now run the first job and you will see in the history both jobs (in my case Job3 and Job4) running one after the other.
Scenario 2: Illustration
The below script will you help you to create two test jobs with the above type of dependency setup between them.
USE [msdb]
GO
/****** Object: Job [Job3] Script Date: 12/26/2014 19:29:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/26/2014 19:29:15 ******/
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'Job3',
@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'<LoginName>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step] Script Date: 12/26/2014 19:29:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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'SELECT 1',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Call Next Step] Script Date: 12/26/2014 19:29:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call Next Step',
@step_id=2,
@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'EXEC dbo.sp_start_job @Job_name = ''Job4''',
@database_name=N'msdb',
@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
/****** Object: Job [Job4] Script Date: 12/26/2014 19:29:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/26/2014 19:29:15 ******/
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'Job4',
@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'<LoginName>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step] Script Date: 12/26/2014 19:29:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step',
@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'SELECT 1',
@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
Replace <LoginName> with actual value as in previous case
Summary
The above article depicts with example scripts how you can setup dependencies between different SQLAgent jobs based on your scenario to make sure the jobs execute and do the data processing in sequence. This would come handy in cases like data warehouse processing where one layer jobs have to finish processing before moving onto the next set of jobs.
See Also
sp_start_job procedure
MSDN documentation
sysjobactivity
MSDN documentation