Monday, April 12, 2010

Job still runs inspite of disabling


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.,

UPDATE sysjobs
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.

UPDATE sysjobs
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.

EXEC msdb.dbo.Sp_update_job
  @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)
FROM   sysjobs 

View the results of the script in text mode ( instead of grid)
to get the script correctly.

7 comments:

Anonymous said...

Just ran into this myself and was hoping there was an easy explanation. Thanks!

Anonymous said...

Excellent post. Was updating the sysjobs table but last scheduled run was still firing off. Great explanation and another reason why to use system stored procedures! Good Work!

Nagaraj Venkatesan said...

Thanks Annony 1 and 2 :)

Anonymous said...

thank you very much for this post.

Anonymous said...

Great article was really useful.

Unknown said...

Thanks a lot great help

Anonymous said...

You should not use "GO" lines with dynamic SQL. This is not actually part of the SQL language. It is a convention that SSMS and other tools use to break a script into separate segments submitted as SQL queries in separate requrests. The SQL engine does not actually understand these, and may generate an error.