SQL Server Jobs - Run Dynamically with Logical Schedule

Question

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!

Answer 1

Hello nbasra,

According to your description, we still recommend you to use SSMS to schedule your jobs.

To do so, in SSMS we need to right click on your Maintenance Plan under "Management" and "Maintenance Plans", and then choose ‘’Modify". Click the calendar icon beside your job name in the tab and then change the setting manually.

One important note is that you need to schedule the excution time of monthly job to be different from others.

For example, you could schedule the exection time of monthly job to be 12 AM and schedule the others to be 1 AM.

If the response helped, do "Accept Answer" and upvote it.
Best regards
Melissa

Answer 2

Hi Nbasra,

As per your requirement ,I suggest you to use GUI method to schedule your jobs by using Management studio.

Go to Management then right click on your Maintenance Plan and choose Modify option. Click the calendar icon beside your job name in the tab and then change the settings manually as per your requirement.
Here you have to remember both schedule times are should be differ. like 1st monthly job then daily job, better give some buffer time for both schedule times because some time 1st may take more time to execute.

Answer 3

The easiest would be to make the jobs into job steps in the same job. Having one job that does not start until the first has finished is nothing that SQL Agent supports out of the box. You would more or less have the first job to start the second.

In your post you have a discussion about GO that suggests that you don't really have an understanding of GO and /or jobs. GO is a batch separator, and it is a convention used by SSMS and other query tools to split up a script in batches. That is, SSMS sends the text up to the first GO and waits for response from SQL Server and then sends the next batch and so on. But this is irrelevant here. "Response" here means that the job has been started, not that has completed. That is, with or without GO, the result is the same.

I would also agree with the other posters that using the UI may be the best to go. Generally, I don't use the UI in SSMS a lot, but I do most things through direct queries in a query window. But for jobs I use the UI, and it is not often that I create or start jobs by running queries.

Answer 4

GO is an SSMS keyword to separate batchs, not a TSQL command.

sp_start_job runs jobs asynchronously. It does not wait for the job to finish.

Please see:
https://www.mssqltips.com/sqlservertip/2167/custom-spstartjob-to-delay-next-task-until-sql-agent-job-has-completed/

Answer 5

DECLARE @now DATETIME = GetDate()
IF Day(@now )=1 BEGIN
PRINT 'Run Job For the 1st day'
END ELSE BEGIN
PRINT 'Run daily Job'
END

In a subplan there is a task "Execute SQL Server Agent Job Task". In the end of Monthly Job execute daily job. In this case you don't need to know when Monthly Job ended. Monthly executes Daily after completion of Monthly.

Don't forget to connect arrow - it makes synchronization.