How to Disable/Enable All SQL Server Agent Jobs in SQL Server

How to Disable/Enable All SQL Server Agent Jobs in SQL Server

Learn how to enable or disable each SQL Server Agent job in this post. This happens frequently when we need to perform maintenance or when we are moving servers.

Several system tables that SQL Server provides give us information about SQL Server objects. We have tables in the MSDB system database that are available for jobs and schedules.

--Get Information for all SQL Server Agent Jobs 
USE msdb
GO 
SELECT * FROM dbo.sysjobs

This error updating the enabled field in the sysjobs table to 0 is frequently made by users. By doing that, you will be able to visually see that the jobs are disabled but that they will continue to execute if the schedule is enabled. You must also disable the schedule for task in the sysschedules tables if you choose to proceed in this manner.

--Wrong Way to disable SQL Server Agent Jobs.
--If Schedule will be enabled, Job will keep Running. 
UPDATE dbo.sysjobsSET enabled=1
 --Update the related schedule as well if you want to disable by updating sysjobs table 
SELECT * FROM dbo.sysschedules


Use of the Sp update job stored procedure is the proper method for enabling and disabling jobs. Using this stored process removes the need to disable the job's scheduling. To generate disable/enable script for all jobs, use the script below. Simply replace @enabled=0 with disable and @enabled=1 with enabled in the script below.

--Right Way to Disable/Enable Jobs. Generate Scripts for Enable/Disable 
SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 1;' 
FROM dbo.sysjobs