My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 completes.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
I realize this question is a few years old at this point, but recently needed to schedule multiple daily maintenance jobs to run overnight in a specific sequence and came up with another way to accomplish this aside from the answers already provided. It's quick to set up and easy to reorder or change jobs as needed through the master job properties.
Not certain how this measures up to best practices, but it does log history for each job individually in addition to showing the combined execution time for all jobs in the master job history. I used this approach because each job in the sequence had different Notification settings (different recipient groups) so I couldn't run each job as an individual step within one single job.
Create user-defined table type.
CREATE TYPE [dbo].[JobSequenceTable] AS TABLE( [JobNumber] [TINYINT] IDENTITY(1,1) NOT NULL, [JobName] [VARCHAR](100) NULL )
Create function to check job status (currently running or not).
CREATE FUNCTION [dbo].[fnJobStatusCheck] (@JobName VARCHAR(100)) RETURNS BIT AS BEGIN DECLARE @IsRunning BIT IF EXISTS ( SELECT J.[name] AS job_name FROM msdb.dbo.sysjobs J INNER JOIN msdb.dbo.sysjobactivity A ON A.job_id = J.job_id WHERE A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC ) AND A.start_execution_date IS NOT NULL AND A.stop_execution_date IS NULL AND J.[name] = @JobName ) BEGIN SET @IsRunning = 1 END ELSE BEGIN SET @IsRunning = 0 END RETURN @IsRunning END GO
Create procedure to run a list of jobs in provided sequence.
CREATE PROCEDURE [dbo].[sp_start_job_sequence] ( @JobList JobSequenceTable READONLY ) AS SET NOCOUNT ON BEGIN DECLARE @JobNumber TINYINT = 1 ,@JobName VARCHAR(100) ,@IsRunning BIT BEGIN WHILE (@JobNumber <= (SELECT MAX(JobNumber) FROM @JobList)) BEGIN SELECT @JobName = JobName FROM @JobList WHERE JobNumber = @JobNumber SELECT @IsRunning = dbo.fnJobStatusCheck(@JobName) IF @IsRunning = 0 BEGIN EXEC msdb.dbo.sp_start_job @job_name = @JobName END WAITFOR DELAY '00:00:15.000'; SELECT @IsRunning = dbo.fnJobStatusCheck(@JobName) WHILE @IsRunning = 1 BEGIN WAITFOR DELAY '00:00:15.000'; SELECT @IsRunning = dbo.fnJobStatusCheck(@JobName) END SET @JobNumber = @jobNumber + 1 END END END GO
Create master SQL job to execute existing jobs in sequence.
DECLARE @JobList AS JobSequenceTable INSERT INTO @JobList VALUES ('Job_Name_1') ,('Job_Name_2') ,('Job_Name_3') ,('Job_Name_4') ,('Job_Name_5') ,('Job_Name_6') ,('Job_Name_7') ,('Job_Name_8') ,('Job_Name_9') ,('Job_Name_10') EXEC dbo.sp_start_job_sequence @JobList
You can follow a master slave model .. where in you would create a master job that drives the jobs.
so create a master job with below steps
--- step 1 EXEC dbo.sp_start_job N'Your_JOB_1' ; GO --- To be 100% accurate, msdb.dbo.sysjobactivity can be queried to make sure a particular job is completed before starting another one. --- step 2 EXEC dbo.sp_start_job N'Your_JOB_2' ; GO --- step 3 EXEC dbo.sp_start_job N'Your_JOB_3' ; GO --- step 4 EXEC dbo.sp_start_job N'Your_JOB_4' ; GO
Your 10 jobs will be there which will be driven by your master job.
Make sure you do proper error reporting. e.g. if Job 1 fails, then just fail the entire job or go to some other step to get you notified .. something along those lines.
I have not yet found a notification or activation on job completion. The nearest I can think of is putting a tirgger on the job-related systems tables in MSDB. This is very dodgy, however, and not at all recommended, and may not be possible.
In the past, when I've needed to merge two job streams that I can't edit, I've used a check-and-wait stored procedure like this:
create procedure dbo.WaitForJob @JobName sysname as set nocount on; while exists( select 1 from msdb.dbo.sysjobactivity ja inner join msdb.dbo.sysjobs j on j.job_id = ja.job_id where j.name = @JobName and ja.start_execution_date < GETDATE() and ja.stop_execution_date is NULL ) begin WAITFOR DELAY '00:00:15'; -- whatever duration you're OK with end -- Check @JobName's status and RAISERROR on failure go
I'm sure you can add in the appropriate actions for when the monitored job has failed. You may also like to add in a timeout so this SP doesn't run indefinitely. This may be one of the exceedingly rare occasions where it is appropriate to use the
sp_ prefix. I'll leave that to your discretion.
Now the T-SQL to run your existing jobs becomes
exec dbo.sp_start_job @job_name = 'job1'; exec dbo.WaitForJob @JobName = 'job1'; exec dbo.sp_start_job @job_name = 'job2'; exec dbo.WaitForJob @JobName = 'job2'; ... exec dbo.sp_start_job @job_name = 'job10'; -- no need to wait
.. with appropriate error handling and return code checking, of course. This can be packaged as a single batch, a job with one step per triggered job or one step per statement. I prefer the last as restart is simpler.
I wrote the below which I have since parameterised and works just fine.
DECLARE @RunInd CHAR(1); SET @RunInd = (SELECT CASE WHEN sja.start_execution_date IS NULL THEN 'N' --Indicating Not Running WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'R' --Indicating Running WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'N' --Indicating Not Running END AS 'RunStatus' FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) AND sj.Name IN ('Job_That_Is_Running')) WHILE @RunInd = 'R' BEGIN WAITFOR DELAY '00:00:30' SET @RunInd = (SELECT CASE WHEN sja.start_execution_date IS NULL THEN 'N' --Indicating Not Running WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'R' --Indicating Running WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'N' --Indicating Not Running END AS 'RunStatus' FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) AND sj.Name IN ('Job_That_Is_Running')) END EXEC msdb.dbo.sp_start_job @job_name='Next_Job_to_Start'
For extremely powerful job workflow management use a SSIS workflow job. Unlike SQL Agent Jobsteps, Workflow tasks can run in parallel, wait to sync up, then execute other steps. You are not limited to Success/Failure to determine the next step. You can add all sorts of functions that let you make decisions. From simple, only do this on a Sunday, to powerful queries into your control tables to make decisions. The Graphic design tool makes it much easier to follow complex logic. SSIS is not just for data movement.
While SSIS does provide some nice job monitoring. You can really step it up with PragmaticWorks (now SentryOne) tool called BIxPress. This lets operations folk watch task execution in real-time. I've used this extensively in large corporate data warehouses. The ability to drill into tasks that failed overnight made troubleshooting a breeze.
Disclaimer: I have never worked for these guys. But knew them as MS Partners.
AS others have posted, this is an old question, but I don't see where anyone has suggested this method yet, so here goes.
At the end of your first job, add one step that calls Job #2, then so on and so forth.
If you need the next job to run REGARDLESS of whether or not a step in Job #1 fails, change it so that when a step fails, it jumps to the appropriate job step (either the next in the line or to Job #2).