When one has to disable a job/group of jobs, common way is to do it from
Enterprise manager or using the Graphical user interface. At times, when one is
trying to update for a group of jobs then one way people attempt to do it
is by updating sysjobs system table directly. ie.,
SET enabled = 0
WHERE name IN ( 'job a', 'job b', 'job c' )
This is a very dangerous way of doing it, as it doesn't disable completely.
Worse is that when you update sysjobs directly,the job appears to be disabled
to on the GUI, but still the job continues to run as scheduled.
For example consider the following job Test.
The job is currently enabled.
SET enabled = 0
WHERE name = 'test'
After executing the query provided above, the job appears to be disabled as shown below.
However, the job continues to run as scheduled. The reason is that the properties
and other attributes of the job are kept in memory and a update to sysjobs
doesnt update the SQL Agent. The correct way of enabling/disabling a job
or updating any property of a job will be to use system stored procedure sp_update_job
Sample call for disabling a job is shown below.
@job_name = 'test',
@enabled = 0
For updating a job schedule use sp_update_schedule/sp_update_jobschedule
For more details refer books online here
Also, just a short script which provides a script to disable all
jobs in your server.
SELECT 'EXEC sp_update_job @job_name = ''' + name + ''',@enabled = 0 ' + CHAR(10) + ' GO ' + CHAR(10)
View the results of the script in text mode ( instead of grid)
to get the script correctly.