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

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

Users frequently update the enabled field in the sysjobs database to 0, which is a mistake. By doing that, you will be able to visually see that the jobs are disabled, but if the schedule is enabled, the job(s) will still execute. If you choose to follow this path, you must also disable the task schedule in the sysschedules tables.

--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


By using the Sp_update_job stored procedure is correct way to enable/disable job. If you use this stored procedure, you don't have to worry about disabling schedule for the job. Below script can be used to generate disable/enable script for all the jobs. You simple have to change @enabled=0 to disable  and @enabled=1 in below script.

--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