1

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?

2
  • Based on my reading of the question, is there a reason that you can't create a single job, with multiple steps? Step 2 fires after step 1 is complete, step 3 after step 2, etc.
    – Nicholai
    May 20, 2015 at 2:28
  • They all have to run at different intervals, one every 10 min one every two hours, and some twice a day. I did find a solution though, and posted it below. May 20, 2015 at 12:28

3 Answers 3

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
0
1

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.

1
  • This does not help the problem I had at all and does not involve scheduling. I ended up finding the answer after another few hours of research and testing. May 19, 2015 at 20:58
1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.