I learned an important lesson this weekend about updating system tables directly. I am getting ready to migrate to a new SQL Server and am setting up agent jobs to match what is on the old server.  I scripted them out and then I ran them on the new server. Then I saw that many of them were enabled.  Rookie mistake! I was in a hurry and googled for an Agent job disable script and found this one:

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

“Awesome”, I thought to myself and kept going.  But over the weekend our on-call started getting alerts of jobs failing from the new server.  How was this possible? They all showed disabled?

Well, it turns out that SQL Agent didn’t know they were disabled because I didn’t use the proper Stored Procedure to update the jobs. SQL Agent thought it was supposed to run them, so it kept running them even though they were marked as disabled. We shut down SQL Agent until I could figure out why they were running and that is one of the fixes.  By “rebooting” the agent, it sees all the jobs are disabled and updates.  For Reference, here is the proper way to update jobs to disabled or enabled From HowardH on SQLServerCentral:

--generate disable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
--generate enable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs WHERE enabled = 0

I added a where clause to the enable script so that if you run them together, you will get all the jobs you should re-enable next time.