I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)

There are several jobs scheduled to run with increments of minutes to days. I want to "turn off" the scheduler so nothing will run on schedule until my manual job is done. I realize that missed jobs will not run again until their next scheduled time, after the scheduler is restarted.

  • First Step: stop scheduler
  • Several Heavy lifting steps: Do the hard work
  • Last Step: start scheduler

I found An Oracle command but I am 100% Microsoft SQL Server

I know that if I Turn off the SQL agent, jobs will not run but that is not what I am looking for.

The jobs that exist for today, are not expected to be the same jobs next time. Disabling jobs with EXEC dbo.sp_update_job is not a viable solution.

Additional information: There may also be jobs in place that are intentionally disabled for some reason, that do want to be re-enabled. Stopping the scheduler seems like the best choice.

  • 1
    Agent is the scheduler. So if you want to stop the scheduler, then you stop Agent. I understand that is it drastic, etc, I'd just like to point out that Agent and the scheduler (you refer to in this context) is the very same thing. – Tibor Karaszi Sep 10 at 11:10
up vote 3 down vote accepted

I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along with the database.schema.table_name_you_want_to_create and it will create the table for the first time for you. After that you can change it into an insert.)

SELECT * FROM 
MSDB.dbo.sysschedules ss
INNER JOIN msdb.dbo.sysjobschedules jss
   ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1

That code returns the schedules for all schedules and it also performs an inner join to limit the data returned to only schedules that are paired to jobs currently that are enabled.

You can then create a loop or cursor or something similar to loop through the staging table and execute the sp_update_schedule procedure. This will disable all schedules that are enabled and paired to a job. Once your maintenance is complete, you can run the loop once more, but this time enabling the schedules you had disabled earlier.

If you would like an example of a loop, you can see an example I have created in the past on stack overflow.

  • This seems like a reasonable plan B. I am really hoping for a way to just disable the scheduler. – James Jenkins Sep 7 at 13:38
  • 2
    I don't believe there is any way to disable the scheduler apart from disabling SQL Agent itself which I don't recommend. I would love to know if there is a way though because that would be interesting. I think you may have to go the more inconvenient route, but maybe someone else has a better idea! – Shaulinator Sep 7 at 13:39

sp_stop_job ?

Link to documentation: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017

  • That would not work for two reasons. One I would have to list the job by name, so I might as well just manually address it. Two, if the job is scheduled to run every 5 minutes, I would have to stop it every 5 minutes – James Jenkins Sep 7 at 13:27

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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