question

nbasra avatar image
0 Votes"
nbasra asked JeffreyWilliams-3310 answered

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
5 |1600 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)
5 |1600 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)
5 |1600 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.

5 |1600 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/

5 |1600 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.

AndreiFomitchev avatar image
0 Votes"
AndreiFomitchev answered AndreiFomitchev edited

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.

5 |1600 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.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

What you can do is use a single agent job with 3 job steps.

Job Step 1: Check if first of month - if first of month, run monthly process
Job Step 2: Check if Saturday - if Saturday, run weekly process
Job Step 3: Check if Saturday - if not Saturday, run daily process

If each process is setup as a stored procedure - you could put the check inside the procedure and just call the procedure for each step. The procedure would exit without processing if it doesn't meet the requirements. That would simplify the agent job setup as you can then just set each step to process without adding any conditional statements.

5 |1600 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.