question

nbasra avatar image
0 Votes"
nbasra asked ·

SQL Server Jobs - Run Dynamically with Logical Schedule

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!







sql-server-generalsql-server-transact-sqlsql-server-integration-services
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @nbasra,

Could you please provide any update about this?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa


0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

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.

18143-untitled.png



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




untitled.png (22.7 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RakeshPonnala-8546 avatar image
0 Votes"
RakeshPonnala-8546 answered ·

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.

18742-image.png

18743-image.png



image.png (27.4 KiB)
image.png (77.6 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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/

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.