By: Ray Barley | Comments (1) | Related: > SQL Server Agent
Problem
In my organization we have hundreds of SQL Agent jobs running daily. Our jobs have many steps, sometimes as many as 50! We would like to implement a solution where we can split our large jobs into multiple smaller ones while respecting the dependencies between the jobs. Our ideal solution would be to automatically launch a SQL Agent job when the job it depends on has completed. We would like the solution to be data-driven where we put these relationships in tables and a SQL Agent job periodically runs to launch jobs when their predecessor has completed. Can you provide a solution?
Solution
In this tip I will demonstrate a simple way of launching a SQL Agent job when the job it depends on completes successfully. The following are the main tables that store the underlying data:
The following are the steps to insert data into the above tables to implement the SQL Agent job launching solution:
- Insert rows into the JOB_MONITOR_EVENT table for the events to monitor.
I have two rows with the following descriptions:
- JOB COMPETED SUCCESSFULLY
- JOB STEP COMPLETED SUCCESSFULLY
- Insert rows into the JOB_MONITOR table to specify the SQL Agent jobs you want to monitor. If you want to monitor for a job step that completed successfully, you have to supply the JOB_STEP_UID in addition to the JOB_ID.
- Insert rows into the JOB_MONITOR_EVENT_OPTION table to specify the SQL Agent job to launch. There is a one to many relationship between the JOB_MONITOR table and the JOB_MONITOR_EVENT_OPTION table.
- The stored procedure ADD_JOB_TO_MONITOR (described below) performs steps 2 and 3.
- The stored procedure QUEUE_JOBS_TO_LAUNCH (described below) queries the completed jobs and job steps, determines the job(s) to launch based on the JOB_MONITOR_EVENT_OPTION table, and inserts rows into the JOB_LAUNCH_QUEUE table.
- The stored procedure LAUNCH_JOBS_FROM_QUEUE (described below) iterates through the rows in the JOB_LAUNCH_QUEUE table and launches the SQL Agent jobs.
I will walk through the implementation in the sections that follow.
Demo Scenario
Before digging in to the implementation details, I want to provide a demo scenario that I will reference. I have the following SQL Agent jobs that I will use as a demo:
- SAMPLE DAILY ETL
- SAMPLE DAILY CUBE PROCESS – launch when the SAMPLE DAILY ETL job completes successfully
- SAMPLE REFRESH DASHBOARDS – launch when step 1 of the SAMPLE DAILY CUBE PROCESS completes successfully
- The following queries extract the details from the SQL Agent job tables:
SELECT [name] , [job_id] FROM [msdb].[dbo].[sysjobs] WHERE [name] LIKE 'SAMPLE%' SELECT [step_id] , [step_name] , [step_uid] FROM [msdb].[dbo].[sysjobsteps] WHERE [job_id] = '9359E080-2084-4218-8F07-4A39071065D4' ORDER BY [step_id]
The following are the results from the above queries:
The main points are:
- Rows 1 – 4 show the sample SQL Agent job names and job ids.
- Rows 7 – 9 show the job steps for the SAMPLE DAILY CUBE PROCESS SQL Agent job.
For demonstration purposes, the SQL Agent jobs only have Transact-SQL script job steps that execute the T-SQL command WAITFOR DELAY '00:01'. I just need the jobs to do something.
Implementation
In this section I will drill in to the implementation details. I have created the following stored procedures:
- ADD_JOB_TO_MONITOR – sets up a SQL Agent job that you want to monitor, specifies the event that you want to monitor, and the SQL Agent job you want to launch when the event occurs
- QUEUE_JOBS_TO_LAUNCH - check the latest SQL Agent jobs and job steps that have completed, determine what SQL Agent jobs should be launched, and insert rows into the [dbo].[JOB_LAUNCH_QUEUE] table
- LAUNCH_JOBS_FROM_QUEUE – start the SQL Agent jobs identified in step 2 in the [dbo].[JOB_LAUNCH_QUEUE] table
In the following sections I will walk through these stored procedures.
ADD_JOB_TO_MONITOR
The ADD_JOB_TO_MONITOR stored procedure performs the configuration required to launch a SQL Agent job when a particular job completes successfully or a job step within a job completes successfully.
The following examples setup the demo scenario described in the previous section:
-- Launch SAMPLE DAILY CUBE PROCESS job when SAMPLE DAILY ETL -- job completes successfully EXEC [dbo].[ADD_JOB_TO_MONITOR] @JOB_ID_TO_MONITOR = '796A3A71-4B93-4DBE-9466-C4BD714C8681' , @JOB_ID_TO_LAUNCH = '9359E080-2084-4218-8F07-4A39071065D4' , @JOB_MONITOR_EVENT_ID = 1 -- Launch SAMPLE REFRESH DASHBOARDS job when step 1 in -- SAMPLE DAILY CUBE PROCESS job completes successfully. EXEC [dbo].[ADD_JOB_TO_MONITOR] @JOB_ID_TO_MONITOR = '9359E080-2084-4218-8F07-4A39071065D4' , @JOB_ID_TO_LAUNCH = '737B2D39-D165-4DCC-9A60-D3C3D773B232' , @JOB_MONITOR_EVENT_ID = 2 , @JOB_STEP_UID_TO_MONITOR='88A30659-392A-4F14-8AF7-FDF243C540F3' , @RUN_ONCE = 1
It’s a little awkward to work with the UNIQUEIDENTIFIER values (i.e. the job_id from the msdb.dbo.sysjobs table), but the benefit is that these values do not change while the job name can easily be changed.
The following query and results show the setup:
-- list jobs / job steps monitored and jobs to launch SELECT j.[name] AS [MONITOR_JOB_NAME] , js.[step_name] AS [MONITOR_JOB_STEP_NAME] , e.[JOB_MONITOR_EVENT_DESCRIPTION] , j2.[name] AS [JOB_NAME_TO_LAUNCH] FROM [dbo].[JOB_MONITOR_EVENT_OPTION] o JOIN [dbo].[JOB_MONITOR] m ON m.[JOB_MONITOR_ID] = o.[JOB_MONITOR_ID] JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = m.[JOB_ID] JOIN [dbo].[JOB_MONITOR_EVENT] e ON e.[JOB_MONITOR_EVENT_ID] = o.[JOB_MONITOR_EVENT_ID] LEFT JOIN [msdb].[dbo].[sysjobsteps] js ON js.[job_id] = m.[JOB_ID] AND js.[step_uid] = m.JOB_STEP_UID JOIN [msdb].[dbo].[sysjobs] j2 ON j2.[job_id] = o.[JOB_ID_TO_LAUNCH];
QUEUE_JOBS_TO_LAUNCH
The QUEUE_JOBS_TO_LAUNCH stored procedure performs the following steps:
- Query the SQL Agent [msdb].[dbo].[sysjobhistory] table to get jobs and job steps that have completed successfully since the last time the stored procedure ran.
- Insert the rows retrieved from the [msdb].[dbo].[sysjobhistory] table into the [dbo].[JOB_MONITOR_JOB_HISTORY_STAGING] table.
- Insert a row into the [dbo].[JOB_LAUNCH_QUEUE] table for each SQL Agent job to be launched based on jobs and job steps that completed successfully and the data in the various JOB MONITOR tables.
- Insert a row into the [dbo].[QUEUE_JOB_LOG] table to record the MAX([instance_id]) from the [msdb].[dbo].[sysjobhistory] table and the number of rows inserted into the [dbo].[JOB_LAUNCH_QUEUE] table.
- The next time the stored procedure runs, it looks for rows in the [msdb].[dbo].[sysjobhistory] table where the [instance_id] is greater than the MAX([instance_id]) in the [dbo].[QUEUE_JOB_LOG] table.
This is a lengthy stored procedure so I’m going to just focus on the code that determines the SQL Agent jobs to be launched based on the successful completion of jobs and job steps. Here is the code that inserts a row into the [dbo].[JOB_LAUNCH_QUEUE] table for a job that is to be launched based on the successful completion of a job:
--
-- queue any jobs that need to be launched based on
-- jobs completed successfully
--
INSERT [dbo].[JOB_LAUNCH_QUEUE] (
[JOB_MONITOR_EVENT_OPTION_ID]
)
SELECT
o.[JOB_MONITOR_EVENT_OPTION_ID]
FROM [dbo].[JOB_MONITOR_JOB_HISTORY_STAGING] s
JOIN [dbo].[JOB_MONITOR] m
ON m.JOB_ID = s.JOB_ID
JOIN [dbo].[JOB_MONITOR_EVENT_OPTION] o
ON o.JOB_MONITOR_ID = m.JOB_MONITOR_ID
JOIN [dbo].[JOB_MONITOR_EVENT] e
ON e.[JOB_MONITOR_EVENT_ID] = o.[JOB_MONITOR_EVENT_ID]
WHERE m.[ENABLED] = 1
AND o.[ENABLED] = 1
AND s.[step_id] = 0 -- Job Completion
AND (
-- JOB COMPLETED SUCCESSFULLY
(e.JOB_MONITOR_EVENT_ID = 1 AND s.[run_status] = 1)
)
Here is the code that inserts a row into the [dbo].[JOB_LAUNCH_QUEUE] table for a job that is to be launched based on the successful completion of a job step:
-- queue any jobs that need to be launched based on a
-- job step that completed successfully - check [JOB_STEP_UID]
-- from JOB_MONITOR
INSERT [dbo].[JOB_LAUNCH_QUEUE] (
[JOB_MONITOR_EVENT_OPTION_ID]
)
SELECT
o.[JOB_MONITOR_EVENT_OPTION_ID]
FROM [dbo].[JOB_MONITOR_JOB_HISTORY_STAGING] s
JOIN [msdb].[dbo].[sysjobsteps] j
ON j.job_id = s.JOB_ID AND j.step_id = s.STEP_ID
JOIN [dbo].[JOB_MONITOR] m
ON m.JOB_ID = s.JOB_ID AND m.JOB_STEP_UID = j.step_uid
JOIN [dbo].[JOB_MONITOR_EVENT_OPTION] o
ON o.JOB_MONITOR_ID = m.JOB_MONITOR_ID
JOIN [dbo].[JOB_MONITOR_EVENT] e
ON e.[JOB_MONITOR_EVENT_ID] = o.[JOB_MONITOR_EVENT_ID]
WHERE m.[ENABLED] = 1
AND o.[ENABLED] = 1
AND s.[step_id] > 0 -- Job Step Completion
AND (
-- JOB STEP COMPLETED SUCCESSFULLY
(e.JOB_MONITOR_EVENT_ID = 2 AND s.[run_status] = 1)
)
LAUNCH_JOBS_FROM_QUEUE
The LAUNCH_JOBS_FROM_QUEUE stored procedure iterates through the rows in the [dbo].[JOB_LAUNCH_QUEUE], joins to the [dbo].[JOB_MONITOR_EVENT_OPTION] table, and launches the SQL Agent jobs per the [JOB_ID_TO_LAUNCH] column. The built-in stored procedure [msdb].[dbo].[sp_start_job] is used to start a SQL Agent job.
This is illustrated in the following piece of code from the stored procedure:
WHILE @DONE = 0
BEGIN
SELECT TOP 1
@JOB_LAUNCH_QUEUE_ID = q.[JOB_LAUNCH_QUEUE_ID]
, @JOB_MONITOR_EVENT_OPTION_ID = q.[JOB_MONITOR_EVENT_OPTION_ID]
, @JOB_ID_TO_LAUNCH = o.[JOB_ID_TO_LAUNCH]
, @RUN_ONCE = o.RUN_ONCE
FROM [dbo].[JOB_LAUNCH_QUEUE] q
JOIN [dbo].[JOB_MONITOR_EVENT_OPTION] o
ON o.[JOB_MONITOR_EVENT_OPTION_ID] = q.[JOB_MONITOR_EVENT_OPTION_ID]
WHERE q.[JOB_LAUNCH_TIME] IS NULL
ORDER BY q.[QUEUE_TIME];
IF @@ROWCOUNT = 0
SET @DONE = 1;
ELSE
BEGIN
-- start the SQL Agent job
EXEC @RC = [msdb].[dbo].[sp_start_job]
@job_id = @JOB_ID_TO_LAUNCH;
Demo
Now that I have completed covering the implementation details, it’s time for a demo. The following are the steps to demo this tip based on my description in the Demo Scenario section above:
-- Execute the SQL Agent job SAMPLE DAILY ETL -- When the SAMPLE DAILY ETL job completes, run this -- stored procedure to add SQL Agent jobs to the queue EXEC [dbo].[QUEUE_JOBS_TO_LAUNCH] -- Launch jobs from the queue. EXEC [dbo].[LAUNCH_JOBS_FROM QUEUE] -- You will see the message -- Job 'SAMPLE DAILY CUBE PROCESS' started successfully. -- When step 1 of the SAMPLE DAILY CUBE PROCESS job -- completes, run these next 2 stored procedures: EXEC [dbo].[QUEUE_JOBS_TO_LAUNCH] EXEC [dbo].[LAUNCH_JOBS_FROM QUEUE] -- You will see the message Job 'SAMPLE REFRESH DASHBOARDS' -- started successfully.
Next Steps
I have now demonstrated how you can implement the code to launch SQL Agent jobs based on a job or job step completing successfully.
The sample code contains the T-SQL scripts to create the tables and stored procedures referenced in this tip. The sample code is packaged in a SQL Server Database project using SQL Server Data Tools (SSDT) 2017. You can create the sample SQL Agent jobs or use your own existing SQL Agent jobs. Either way, you will have to edit the code in the ADD_JOB_TO_MONITOR section above with the UNIQUEIDENTIFIER values for your jobs.
The following are the next steps:
- SQL Agent is a great tool. Check out the SQL Server Agent Tip category on MSSQLTips.com.
- There are many interesting things you can do to enhance SQL Agent. This tip provides a useful example.
- Create a SQL Agent job to execute the [dbo].[QUEUE_JOBS_TO_LAUNCH] and the [dbo].[LAUNCH_JOBS_FROM_QUEUE] stored procedures and add a schedule to automatically launch SQL Agent jobs as their predecessor jobs and job steps complete successfully.
- Take a look at Publish the database using SSDT to see how to create the tables and stored procedures in your database using SSDT
- Download the code from this tip here and experiment.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
