Why did Disabled Agent jobs still run. (ss2005)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Why did Disabled Agent jobs still run. (ss2005)

Author

Topic

Analyzer
Posting Yak Master

115 Posts

Posted - 2011-04-05 : 12:02:10

Hi,

Had to disable 100+ backup jobs in SQL Agent over weekend. Ran below command, refreshed numerous times and verified jobs were ''disabled''. Discovered Monday morning the jobs had ran from job history and SQL Logs. (jobs were still disabled)

Any idea how disabled jobs can still run? Other DBAs at my clients site have experienced same issue with this particular instance.

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-05 : 13:12:10

That is not how you should go about disabling jobs. You should never update system tables directly especially when there is a supported way of doing the task.

Here's an old blog post that I have which has the right code to disable all jobs: http://weblogs.sqlteam.com/tarad/archive/2003/10/17/325.aspx

Perhaps if you had followed the proper procedure to disable jobs, these jobs may not have run. I really can't say for sure though. Disable them properly and let's see what happens.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Analyzer
Posting Yak Master

115 Posts

Posted - 2011-04-06 : 04:58:13

Thanks Tara,

Had to find script fast, script I provided was the first one that came up on the search. Visually it worked when deployed on test server and all jobs ''disabled''.

However jobs still ran. Will run some test's with the script you provided and provide feedback to this thread.

Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-06 : 12:57:19

I have a feeling that the way you disabled the jobs would have also required the agent to be restarted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-06 : 16:43:06

SQL Server Agent caches job settings and parameters. Using the procedure 'sp_update_job' will notify SQL Server Agent that the job has been updated and refreshes the cache.

So, the reason your jobs still executed as if they were scheduled is because the agent was not notified that the jobs had been updated.

Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-06 : 16:46:12

Thanks Jeff, I had a feeling that was the case.

Analyzer, you can use my code then to quickly disable the jobs and you won't see this issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Analyzer
Posting Yak Master

115 Posts

Posted - 2011-04-08 : 09:41:27

Tara,

Can confirm your script worked perfectly. I also requested a Microsoft DSE analyse the problem and here is their feedback.

To sum it up, the SQL Agent reads properties and attributes for each job, which are only updated when executing sp_update_job.
Just updating the enabled column in sysjobs won't do the trick, although it correctly alters the display for the jobs…

Thanks for everyones contribution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources