By: Jason E. Bacani
Introduction
When working with SQL Server Agent, one of the many useful features is the ability to schedule SQL Agent Jobs. The frequency and scheduling of such jobs can be as fancy and as specific as needed, and can be coordinated with implementation of a single schedule, or via multiple schedules. This flexibility can be seen similarly in MS Outlook Calendar, as the logic Microsoft implemented there applies to what has been implemented in MS SQL Server. And as with most things in development and programming, there are multiple ways to schedule SQL Agent Jobs to attain the same purpose.
One of the tricks we have utilized here at Key2 with scheduling SQL Agent Jobs is the ability to chain jobs together that are dependent on each other. Yes, while multiple disparate jobs can be combined into multiple steps within one massive job, there may be a need for separate job owners to maintain their own jobs. In this case, job chaining is utilized.
How It Works
Consider a SQL Agent Job titled “Load EDW.” In many cases, we want to run a process to load data into some enterprise data warehouse. The job itself is scheduled to run twice per day, on weekdays, and once each day on weekends. Note, as mentioned at the beginning, the scheduling itself can be implemented creatively with scheduling features on SQL Agent. In this case, let us assume there are two schedules. Schedule A is set at 6 AM server time, seven days a week, and Schedule B is set for the five weekdays at 6 PM server time and never on the weekends. Simple scenario, right?
Chaining jobs comes in to play when a subsequent job needs to be executed upon completion of the “Load EDW” job. This subsequent job is called “Post EDW,” as it happens after the loading of the EDW. Well, we could schedule “Post EDW” to occur at a time when we can predict “Load EDW” completes. However, through chaining, we can essentially add a step in “Load EDW” to simply call “Post EDW”. This is easily accomplished with a T-SQL step that uses the built in msdb stored procedure, dbo.sp_start_job.
As an illustration, here is the SQL Agent Job “Load EDW”:
And here in that job is a step to call “Post EDW”:
When you consider the step to call the job is simply T-SQL that runs an msdb stored procedure, then you have more flexibility to control conditions on when to run job. Consider “Load EDW” being a daily process, yet the requirement is to not run the “Post EDW” job on Sundays. With it being T-SQL, we can add logic as such:
|
1 2 3 4 |
IF DATEPART(WEEKDAY,GETDATE()) <> 1 --Do not run 'Post EDW' on Sundays BEGIN EXEC mdsb.dbo.sp_start_job @job_name = 'Post EDW' END |
Thus, with T-SQL, we can add further conditions on when to run the chained step.
As a caveat, having multiple separate steps that utilize this method will call the subsequent jobs asynchronously; once the step to call sp_start_job succeeds in starting the next job, the step itself is completed. With this in mind, separate steps will fire off one by one, with no dependencies in place as the calling SQL Agent Job only knows the success of starting the job; it has no ties to whether the called job succeeds or not. Care must be taken if those dependencies are needed.
Resources
There are many resources online about sp_start_job, and the use cases and variance in the implementation can be as creative and innovative as needed. Our example is one basic use case, but it can be adapted and modified for many business needs. Aside from this example, here are some additional resources:
• MS SQL Docs: SQL Server Agent
• MS SQL Docs: sp_start_job
• BrentOzar.com: Chaining Agent Tasks For Alerting The Right Way, by Erik Darling
• LogicalRead: Scheduling SQL Server Jobs with SQL Agent, by Michael Otey
• MS TechNet: Setting Dependencies Between SQL Server Agent Jobs
Thanks for Reading!
We at Key2 Consulting are seasoned IT professionals, and we are highly experienced with Data Warehousing and SQL Server, from 2016 and up through the forthcoming 2019 edition. We routinely solve scenarios like this with innovative, creative, and tried and true solutions. Please subscribe to the Key2 mailing list below to learn more about the work we do and what are our colleagues are working on in the industry. And, if you have more questions around this specific topic, please comment below and reach out to us!
Many thanks!
Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.
Hi Jason,
EXEC mdsb.dbo.sp_start_job @job_name = ‘Post EDW’
Should be
EXEC msdb.dbo.sp_start_job @job_name = ‘Post EDW’