February 7, 2014 at 10:27 am
On SQL Server Central and on other forums there are many questions asking how to enable/disable
a job with tsql.
Invariably, the ansWer is exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 1 --Enable
or = 0 to disable. The person asking says thanks, and that is the end of it.
But clearly, that is not quite how it works. My test is SQL2008 R2.
I ran exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0 to disable the job.
Right clicking on the job shows "disable" as an option and "enable" is greyed out.
Opening the properties shows the Enabled box unchecked.
Looking at the Schedule, it is enabled.
So does it run? Yes, if I right click and start it. IT IS NOT TOTALLY DISABLED.
But it won't run as scheduled, even though the schedule is "enabled"
So, ......What exactly does the command do? It doesn't disable the schedule. It doesn't disable the job.
And yet it doesn't run as scheduled. Is there a simple command to totally disable either
the job or the schedule, or is a script required to get it done? there are scripts out there
but I would rather K.I.S.S. if possible.
February 7, 2014 at 10:40 am
there is no way to totally disable a job so it cannot even be run manually. All the disable option does is prevent the job running as a scheduled job.
you would have to delete the job or amend it so it fails or does nothing.
---------------------------------------------------------------------
February 7, 2014 at 10:54 am
Agree with George...
msdb..sp_update_job @job_name = 'Job Name', @enabled = 0 will disable the job, but the respective job schedule will be untouched. By this, the job wont run as scheduled, but we can run the job manually as long as the job exists.
Regarding enable/disable greyed out, see attached screenshot on my SQL Server 2012. Everything is working as expected for me...Since the Job is already disabled, when I right clicked, disable is greyed out.
February 7, 2014 at 11:38 am
You've convinced me that the job will not zombify. But I'd feel better
if I knew what really happens. Nice to know it looks like it is supposed to in 2012
Thanks for the responses
February 7, 2014 at 1:44 pm
SQLAgent is a job scheduler, but for a job to run to a schedule, both the job itself and the schedule have to be enabled. If either are disabled the job does not run unless explicitly executed. Remember a job does not have to a schedule defined for it.
To see what actually happens when you run sp_update_job, open up the code.
there is also sp_update_schedule to manipulate a job schedule.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply