In this post we will learn how to enable/disable all SQL Server Agent jobs. This is very common situation when we have to perform maintenance or we are in process of Migration.
SQL Server provide us different system tables those provide us information related to sql server objects. For jobs and schedules we have tables available in MSDB system database.
--Get Information for all SQL Server Agent Jobs
USE msdb
GO
SELECT * FROM dbo.sysjobs
People often make this mistake, they update the enabled column to 0 in sysjobs table. By doing that you will see graphically the jobs are disabled but job/s will keep running if schedule is enabled. If you want to go by this route, you have to disable the schedule for job in sysschedules tables as well.
--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
SQL Server provide us different system tables those provide us information related to sql server objects. For jobs and schedules we have tables available in MSDB system database.
--Get Information for all SQL Server Agent Jobs
USE msdb
GO
SELECT * FROM dbo.sysjobs
People often make this mistake, they update the enabled column to 0 in sysjobs table. By doing that you will see graphically the jobs are disabled but job/s will keep running if schedule is enabled. If you want to go by this route, you have to disable the schedule for job in sysschedules tables as well.
--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
No comments:
Post a Comment