Needle and the thread Agents running out of it’s head…the disabled agent jobs are still running!

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?

Homer

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.

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s