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
go
reconfigure with override
go
update msdb.dbo.sysjobs
set [enabled] = 0
where [name] = ‘Full Database Backups’
go
set [enabled] = 0
where [name] = ‘Full Database Backups’
go
exec sp_configure ‘allow updates’,0
go
reconfigure with override
go
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
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
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
