Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How can I disable a Job via TSQL Expand / Collapse
Author
Message
Posted Tuesday, October 10, 2006 9:42 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, Visits: 265

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/
Post #314412
Posted Tuesday, October 10, 2006 9:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610
exec msdb..sp_update_job @job_id = 0xC0923E436928064EA33B46B2A47BFF61 , @enabled = 0
Post #314415
Posted Tuesday, October 10, 2006 10:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, Visits: 265
Awesome, thanks very much. 

- Paul

http://paulpaivasql.blogspot.com/
Post #314429
Posted Wednesday, October 11, 2006 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2008 10:04 AM
Points: 3, Visits: 6

It might be easier to use:

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

Paul (polecki@Insurance.com)

 

Post #314739
Posted Wednesday, October 11, 2006 1:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #314743
Posted Wednesday, October 11, 2006 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2008 10:04 AM
Points: 3, Visits: 6

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.

Post #314744
Posted Wednesday, October 11, 2006 1:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610
Ya that's quite a few .
Post #314746
Posted Thursday, April 24, 2008 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:24 AM
Points: 120, Visits: 799
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
Post #489976
Posted Thursday, April 24, 2008 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2008 10:04 AM
Points: 3, Visits: 6
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.
Post #489997
Posted Thursday, April 24, 2008 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:24 AM
Points: 120, Visits: 799
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
Post #490008
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse