August 17, 2020 at 11:53 pm
Hello,
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:
1. If 1st of month - then run Monthly job
2. Then check if this is a Saturday, if so, run weekly next
3. 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.
Thank you!
August 18, 2020 at 2:16 pm
Perhaps something like this would help you?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 18, 2020 at 2:24 pm
You could create a daily agent job with a separate step for each (monthly/weekly/daily) task then add a step to the front of each of those steps to test if the day matches the requirement of the next step. If it does not then raise an error and get the job to skip to the next validation step by setting the next step in the "On failure action".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply