Can you Schedule a job inside another job?

Question

I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a locking situation where two jobs are waiting for each other to finish.

The built in retry wont work because they are considered running while waiting to retry. I could just error out and have the job run every 10min checking when it was last run successfully and if anyone else is running. Then run or error again, but that could lead to jobs happening way off scheduled.

I have looked around but can't find anything, so I don't know if it is even possible but can you set up a one time schedule, like run this job at this time from inside another job?

Or is there a better way of doing this that I have overlooked?

Answer 1

In more research and diving into the msdn site documentation I have found and am now using.

sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule

so when the job sees that others are running ATM I am doing this:

exec msdb.dbo.sp_add_schedule [MSDN link][1]
   @schedule_name = @TempScheduleName,
   @enabled = 1,
   @freq_interval = 1,
   @freq_type = 1,
   @freq_subday_type = 4,
   @freq_recurrence_factor = 1,
   @freq_subday_interval = 5,
   @active_start_date = @todaysdate,
   @active_start_time = @todaystime (plus 5min)


exec msdb.dbo.sp_attach_schedule [MSDN Link][2]
   @job_name = @JobName',
   @schedule_name = @TempScheduleName

then the next time the job is run this is also done to remove the temp schedule:

   exec msdb.dbo.sp_detach_schedule
    @job_name = @JobName,
    @schedule_name = @TempScheduleName

   exec msdb.dbo.sp_delete_schedule
   @schedule_name = @TempScheduleName

Answer 2

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job

E.g. The first job has a schedule and final step of the job is to run:

exec msdb.dbo.sp_start_job N'My Second Job'; 

"My Second Job" doesn't have a schedule, relying on the first job to start it.

Answer 3

With this solution you can easily know which jobs are on the queue to run with a simple select to a table. Also, if the server goes down for some reason, cleaning the jobs that are waiting to run is as easy as a truncate on a table. And still if you want them to run you needen't do anything and they'll run orderly.

1. Create a table (Say, ScheduleLonelyJobsTable) to store information about jobs waiting to be run lonely - One column for job_id and one for datetime_request. The table should be tiny at each moment but you can index on this last column. Optionally add a column stepToRun.
2. The first step in each job that should run lonely will be to insert its job_id and GETDATE() into this table and then jump out (Quit the Job after the step). Optionally insert into the stepToRun column the name of the second step on the job, which actually is the first step executing what you want to do on the job.
3. The second step of all these jobs will contain the 1st action that you want to do on the job. If you added and filled the optional column stepToRun, then you needn't do anything more. Otherwise, give this second step a standard name like "LonelyJobStartStep".

4. Create a new job that will be used to manage all the lonely jobs, running with the desired frequency (for instance every 10 seconds). This job will have just one step, which is to execute the following query inside a if that checks if no job is running:

   DECLARE @job_id_torun uniqueidentifier SET @job_id_torun = ( SELECT TOP 1 job_id FROM dbo.ScheduleLonelyJobsTable ORDER BY datetime_request) IF @job_id_torun IS NOT NULL BEGIN -- If a job gets twice on the table, both entries will be removed. If this is not the desired behaviour just delete the oldest entry DELETE FROM dbo.ScheduleLonelyJobsTable WHERE job_id = @job_id_torun -- Obtain the @step_name from the ScheduleLonelyJobsTable table if you added the optional column, otherwise just use a standard name like bellow EXEC msdb.dbo.sp_start_job @job_id=@job_id_torun, @step_name = 'LonelyJobStartStep'; END