In SQL 2016, I currently have monthly, weekly and daily maintenance plans that run on a fixed schedule. As a new requirement, I need to run it more dynamically than on a fixed time.
The plan is:
If 1st of month - then run Monthly job
Then check if this is a Saturday, if so, run weekly next
Else if Sun~Fri, run daily next
I can create this logic with T-SQL and generate an output to execute:
EXECUTE sp_executesql @JobsToRun
There is no issue if this is not first of the month and there is only one maintenance job to run (weekly or daily) ...
EXEC MSDB.dbo.sp_start_job N'Daily.Subplan_1'
However, it becomes an issue, if the output has two Execute statements, first to run the monthly and then next run weekly/daily ...
EXEC MSDB.dbo.sp_start_job N'Monthly.Subplan_1' GO EXEC MSDB.dbo.sp_start_job N'Daily.Subplan_1' GO
The issue is that I cannot use "GO" from output and execute that, as shown above as it results in an error:
Incorrect syntax near 'GO'.
and without a "GO", both Execute statements kick off at the same time since there is no separation of batches.
EXEC MSDB.dbo.sp_start_job N'Monthly.Subplan_1' EXEC MSDB.dbo.sp_start_job N'Daily.Subplan_1'
It is very important that the monthly finishes first and then kicks off the next job. I would like to know if there is a better way to achieve this than creating and calling a custom SP to monitor the first job status etc.
I have also tried creating multiple steps in Maintenance plans for each condition, but SQL considers a step successful after "Executing" the agent job rather than waiting for its completion and then moving onto the next step/job.