Post reply

How can I disable a Job via TSQL

  • October 10, 2006 at 9:42 am

    #173167

    I want to do an upgrade on a busy SQL 2000 server that has lots of jobs kicking off at different times.  But first I need to wait until there are no jobs running before I shut down the SQL Agent. 

    My plan was to make a copy of sysjobs, then I did

    UPDATE sysjobs SET Enabled = 0

    To my surprise the jobs kept executing.  When I queried the table, sure enough the Enabled column (smallint) was all zeros.  I'm thinking the Agent must have a cache somewhere which needs to be refreshed, but I don't know how to do that.

    Any ideas on how to disable my jobs via TSQL?

    Thanks

    Paul

    - Paul

    http://paulpaivasql.blogspot.com/

  • October 10, 2006 at 9:52 am

    #665042

    exec msdb..sp_update_job @job_id = 0xC0923E436928064EA33B46B2A47BFF61 , @enabled = 0

  • October 10, 2006 at 10:48 am

    #665052

    Awesome, thanks very much. 

    - Paul

    http://paulpaivasql.blogspot.com/

  • October 11, 2006 at 12:50 pm

    #665287

    It might be easier to use:

    exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0

    Paul (polecki@Insurance.com)

     

  • October 11, 2006 at 1:07 pm

    #665290

    Good point... I used the profiler to find out what sql enterprise manager was generating to disable the job. But using the jobname is certainly much simpler.

  • October 11, 2006 at 1:14 pm

    #665291

    The way I learned some of the s/p's was to right click on the job and choose All tasks...Generate SQL script.  That shows many s/p's used to create a job on another server.

  • October 11, 2006 at 1:16 pm

    #665293

    Ya that's quite a few .

  • April 24, 2008 at 8:31 am

    #806619

    Hi All

    This is all well, but isnt there a script that loops through all the jobs and disable them all at once.

    Thanks in advance

  • April 24, 2008 at 8:47 am

    #806637

    Depending on why you want to turn off all jobs, we usually just stop Sql Server Agent. We would do this if the source server was going to be down.

    When we are ready to resume the jobs, we start it back up. Then we might have to manually run any jobs which needed to run during the outage.

  • April 24, 2008 at 8:52 am

    #806647

    Thanks for your reply Paul

    Its a 3 phase plan we are going through.1)First put db in single-user mode.2)Then switch off the system and 3)then decomission the system.

    We cant disable SQL Agent, we still need to backup the system dbs.

    Thanks and Regards

    A

  • May 14, 2008 at 3:17 am

    #815004

    Can you do this on a remote server? We have a mirrored database and I want to write a script to enable and disable jobs on both servers in the event of a failover (the script to be run manually).

    --
    Scott

  • August 11, 2009 at 3:54 am

    #1037305

    thanks very helpful

  • August 16, 2011 at 11:46 am

    #1369229

    SELECT job_id,[name] FROM msdb.dbo.sysjobs

    EXEC msdb.dbo.SP_UPDATE_JOB @job_id = 'B9D5575F-56FB-4B34-8180-2728FB70BC0F' , @enabled =1

  • February 13, 2012 at 6:28 pm

    #1446594

    Does this wait if the job is running at the time before executing the next statement if used in a SP? I've got a job running every five minutes checking for a change to a bunch of tables being fed via web services on another server and refreshes a local copy. I'd like to disable the job, refresh the local copy, which sometimes takes just around or more than five minutes, then re-enable the job running every five minutes.

    The timing is a bit too erratic to be tested and I wonder if anyone knows for sure. It would save me considerable time finding out if someone already knew.

    Thanks.

    Joe

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply