How To Disable Multiple SQL Server Agent Jobs!

Hello and welcome back to the trenches of supporting SQL Server in your environments….

Today I thought I would share with you my experience with disabling multiple jobs in SQL Server 2008.  Here is the situation….

The other night I participated in a maintenance window that was to update drivers on our SAN.  So the pre-maintenance work I had to do before shutting down the cluster was to disable all the jobs in each instance.  Now, I am not sure if your systems are like mine… but I am talking about more than 100 jobs on each instance.  Now there is no way I am going to disable these jobs manually.

Now thinking about my days of SQL Server 6.5, 7.0 and 2000 I used to run the following in the MSDB database.

exec sp_configure ‘allow updates’,1
go
reconfigure with override
go

update msdb.dbo.sysjobs
set [enabled] = 0
where [name] = ‘Full Database Backups’
go

exec sp_configure ‘allow updates’,0
go
reconfigure with override
go

So I thought I would go with what I know for SQL Server 2008… BIG MISTAKE!  When I executed the T-SQL code above for this job it did work!  The problem came when I went to enable the job.  To enable the job I ran the same T-SQL above but change [enabled] = 1 and the job “appeared” to have been enabled.  The job listing in SQL Server Agent went from the color grey (disabled) to the color blue (enabled) and I thought everything was good.  Until the schedule for this job came and gone and the job did not run.  So I was like… uummmm…whats going on?

So I did some research and it turns out when you access (by that I mean update) the system tables directly specifically the msdb.dbo.sysjobs table some functionality does not get re-enabled.. e.g. Job schedules.  To fix this issue, you need to manually disable the job and jobs schedule manually and then re-enable both manually.  If you need to do this for the number of jobs that I have… it will take you a long time and chance for missing a job.

So now I was stuck… I had over a hundred jobs to fix and a short amount of time to do it in.  So I went online, used BOL and I came across the stored procedure sp_update_job stored procedure.  This procedure will enable the job and reset (reactivate) the schedule on the job.  So I wrote a T-SQL script that creates a T-SQL statement for every job I have in msdb.dbo.sysjobs that calls the sp_update_job procedure.  To make sure this works, I went ahead and disabled the jobs again through this method so that when I re-enable the jobs again through this stored procedure the issues will be resolved.  Here is the T-SQL script that creates the statements to first disable the jobs:

select ‘exec msdb.dbo.sp_update_job @job_name = ”’ + name + ”’, @enabled = 0′
from msdb.dbo.sysjobs
order by name

Here is the T-SQL script that creates the statements to enable the jobs:
select ‘exec msdb.dbo.sp_update_job @job_name = ”’ + name + ”’, @enabled = 1′
from msdb.dbo.sysjobs
order by name

Take the result set from the query above… copy, paste and execute the T-SQL statements that were generated and your jobs will be enabled and their schedules will be reset.

You are now back in business!… I hope this helps… if you have any suggestions or comments please leave them here!

CrazedDBA