Disable / Enable SQL Jobs by T-SQL

Objective
Sometimes on certain situation e.g.:month end closing, we need to disable all our SQL Server jobs then enable it back after that process was finished. If we have tens of jobs even more, I believe it is quite take time if we do it through SQL Server Management Studio / Enterprise Manager.  SQL Server already provide a stored procedure “sp_update_job” on msdb database to change the status of the job (see SQL Server BOL for further information). We just provide the job_id / job_name and the new status (1 = enable, 0 = disable).

Usage
Just run below script to get the TSQL script. You can review the result first before run the result to disable/ enable SQL jobs. FYI, when trying to disable the jobs the script only execute the jobs that status is enable. To enable it back, just keep the result and replace  @enable = 0 become @enable = 1. I just prevent not to enable job which status is disable before the script was executed.

Script

SET NOCOUNT ON DECLARE @job_id UNIQUEIDENTIFIER DECLARE @disable_job CHAR(1) SET @disable_job = 'Y' -- Fill 'Y' to disable job .. 'N' to enable job IF OBJECT_ID('tempdb..#sysjobs') IS NOT NULL DROP TABLE #sysjobs SELECT job_id INTO #sysjobs FROM msdb..sysjobs WHERE enabled = CASE @disable_job WHEN 'Y' THEN 1 ELSE 0 END WHILE (1 = 1) BEGIN SELECT @job_id = job_id FROM #sysjobs IF @@rowcount = 0 GOTO _EXIT PRINT 'exec msdb..sp_update_job @job_id = ''' + CAST(@job_id AS VARCHAR(36))+ ''', @enabled = ' + CASE @disable_job WHEN 'Y' THEN '0' ELSE '1' END PRINT 'GO' DELETE FROM #sysjobs WHERE job_id = @job_id END _EXIT: DROP TABLE #sysjobs SET NOCOUNT OFF

Query For Listing SQL Server Job Schedule

Background
On this occasion I want to share TSQL script to list Job Scheduler in SQL Server 2000 and next version. If using Enterprise Manager (EM), we can access under Management -> SQL Server Agent -> Jobs (see below picture).
 

If using SQL Server Management Studio, it can be accessed under SQL Server Agent -> Jobs (see below picture)

The issue is little difficult for me to review the schedule of the jobs. I have to click the each job and take note of each schedule. Since all these jobs keep on MSDB database, we are able to query the jobs within its schedule. When we have many jobs both maintenance or monitoring job etc, as a DBA we have to review the time when the jobs will be running. It is important to make our SQL Server load is balance which is mean not very heavy in one time but very light in another time.

The jobs general information keep in msdb.dbo.sysjobs table and the schedule keep in msdb.dbo.sysjobschedules (SQL 2000) or msdb.dbo.sysschedules (SQL 2005 and next). Read SQL Server Book Online (BOL) for further information about the columns of these tables.

Script

To make the main script simpler, I create 3 functions. Just run below script on master database. Actually you can it to another database and don’t forget to change the main query. These 3 functions valid for SQL Server 2000 and next. But there is little differences on the main query since there is a difference schema on table msdb.dbo.sysjobschedules.

USE master GO CREATE FUNCTION fn_freq_interval_desc(@freq_interval INT) RETURNS VARCHAR(1000) AS BEGIN DECLARE @result VARCHAR(1000) SET @result = '' IF (@freq_interval & 1 = 1) SET @result = 'Sunday, ' IF (@freq_interval & 2 = 2) SET @result = @result + 'Monday, ' IF (@freq_interval & 4 = 4) SET @result = @result + 'Tuesday, ' IF (@freq_interval & 8 = 8) SET @result = @result + 'Wednesday, ' IF (@freq_interval & 16 = 16) SET @result = @result + 'Thursday, ' IF (@freq_interval & 32 = 32) SET @result = @result + 'Friday, ' IF (@freq_interval & 64 = 64) SET @result = @result + 'Saturday, ' RETURN(LEFT(@result,LEN(@result)-1)) END GO CREATE FUNCTION fn_Time2Str(@time INT) RETURNS VARCHAR(10) AS BEGIN DECLARE @strtime CHAR(6) SET @strtime = RIGHT('000000' + CONVERT(VARCHAR,@time),6) RETURN LEFT(@strtime,2) + ':' + SUBSTRING(@strtime,3,2) + ':' + RIGHT(@strtime,2) END GO CREATE FUNCTION fn_Date2Str(@date INT) RETURNS VARCHAR(10) AS BEGIN DECLARE @strdate CHAR(8) SET @strdate = LEFT(CONVERT(VARCHAR,@date) + '00000000', 8) RETURN RIGHT(@strdate,2) + '/' + SUBSTRING(@strdate,5,2) + '/' + LEFT(@strdate,4) END

Main query for SQL Server 2000
revise: May 18, 2009 : fixed inaccurate next run date

/* Created by Solihin Ho - https://solihinho.wordpress.com Usage : Change the value of variable @Filter 'Y' --> display only enabled job 'N' --> display only disabled job 'A' --> display all job 'X' --> display job which is duration already end */ DECLARE @Filter CHAR(1) SET @Filter = 'A' DECLARE @sql VARCHAR(8000) DECLARE @is_sysadmin INT DECLARE @job_owner sysname IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL BEGIN DROP TABLE #xp_results END CREATE TABLE #xp_results ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL ) SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) SELECT @job_owner = SUSER_SNAME() INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner SET @sql = ' SELECT j.Name AS JobName , c.Name AS Category , CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled , CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled , j.Description , CASE s.freq_type WHEN 1 THEN ''Once'' WHEN 4 THEN ''Daily'' WHEN 8 THEN ''Weekly'' WHEN 16 THEN ''Monthly'' WHEN 32 THEN ''Monthly relative'' WHEN 64 THEN ''When SQL Server Agent starts'' WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs , CASE s.freq_type WHEN 1 THEN ''O'' WHEN 4 THEN ''Every '' + convert(varchar,s.freq_interval) + '' day(s)'' WHEN 8 THEN ''Every '' + convert(varchar,s.freq_recurrence_factor) + '' weeks(s) on '' + master.dbo.fn_freq_interval_desc(s.freq_interval) WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval) + '' of every '' + convert(varchar,s.freq_recurrence_factor) + '' month(s)'' WHEN 32 THEN ''The '' + CASE s.freq_relative_interval WHEN 1 THEN ''First'' WHEN 2 THEN ''Second'' WHEN 4 THEN ''Third'' WHEN 8 THEN ''Fourth'' WHEN 16 THEN ''Last'' END + CASE s.freq_interval WHEN 1 THEN '' Sunday'' WHEN 2 THEN '' Monday'' WHEN 3 THEN '' Tuesday'' WHEN 4 THEN '' Wednesday'' WHEN 5 THEN '' Thursday'' WHEN 6 THEN '' Friday'' WHEN 7 THEN '' Saturday'' WHEN 8 THEN '' Day'' WHEN 9 THEN '' Weekday'' WHEN 10 THEN '' Weekend Day'' END + '' of every '' + convert(varchar,s.freq_recurrence_factor) + '' month(s)'' END AS Occurs_detail , CASE s.freq_subday_type WHEN 1 THEN ''Occurs once at '' + master.dbo.fn_Time2Str(s.active_start_time) WHEN 2 THEN ''Occurs every '' + convert(varchar,s.freq_subday_interval) + '' Seconds(s) Starting at '' + master.dbo.fn_Time2Str(s.active_start_time) + '' ending at '' + master.dbo.fn_Time2Str(s.active_end_time) WHEN 4 THEN ''Occurs every '' + convert(varchar,s.freq_subday_interval) + '' Minute(s) Starting at '' + master.dbo.fn_Time2Str(s.active_start_time) + '' ending at '' + master.dbo.fn_Time2Str(s.active_end_time) WHEN 8 THEN ''Occurs every '' + convert(varchar,s.freq_subday_interval) + '' Hour(s) Starting at '' + master.dbo.fn_Time2Str(s.active_start_time) + '' ending at '' + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency , CASE WHEN s.freq_type = 1 THEN ''On date: '' + master.dbo.fn_Date2Str(active_start_date) + '' At time: '' + master.dbo.fn_Time2Str(s.active_start_time) WHEN s.freq_type < 64 THEN ''Start date: '' + master.dbo.fn_Date2Str(s.active_start_date) + '' end date: '' + master.dbo.fn_Date2Str(s.active_end_date) END as Duration , master.dbo.fn_Date2Str(xp.next_run_date) + '' '' + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date FROM msdb.dbo.sysjobs j (NOLOCK) INNER JOIN msdb.dbo.sysjobschedules s (nolock) ON j.job_id = s.job_id INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id WHERE 1 = 1 @Filter ORDER BY j.name' IF @Filter = 'Y' SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ') ELSE IF @Filter = 'N' SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ') ELSE IF @Filter = 'X' SET @sql = REPLACE(@sql,'@Filter', 'AND s.active_end_date < convert(varchar(8),GetDate(),112) ') ELSE SET @sql = REPLACE(@sql,'@Filter','') EXEC(@sql)

Main Query for SQL Server 2005 and next version

/* Created by Solihin Ho - https://solihinho.wordpress.com Usage : Change the value of variable @Filter 'Y' --> display only enabled job 'N' --> display only disabled job 'A' --> display all job 'X' --> display job which is duration already end */ DECLARE @Filter CHAR(1) SET @Filter = 'A' DECLARE @sql VARCHAR(8000) DECLARE @is_sysadmin INT DECLARE @job_owner sysname IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL BEGIN DROP TABLE #xp_results END CREATE TABLE #xp_results ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) SELECT @job_owner = SUSER_SNAME() INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner SET @sql = ' SELECT j.Name AS JobName , c.Name AS Category , CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled , CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled , j.Description , CASE s.freq_type WHEN 1 THEN ''Once'' WHEN 4 THEN ''Daily'' WHEN 8 THEN ''Weekly'' WHEN 16 THEN ''Monthly'' WHEN 32 THEN ''Monthly relative'' WHEN 64 THEN ''When SQL Server Agent starts'' WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs , CASE s.freq_type WHEN 1 THEN ''O'' WHEN 4 THEN ''Every '' + convert(varchar,s.freq_interval) + '' day(s)'' WHEN 8 THEN ''Every '' + convert(varchar,s.freq_recurrence_factor) + '' weeks(s) on '' + master.dbo.fn_freq_interval_desc(s.freq_interval) WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval) + '' of every '' + convert(varchar,s.freq_recurrence_factor) + '' month(s)'' WHEN 32 THEN ''The '' + CASE s.freq_relative_interval WHEN 1 THEN ''First'' WHEN 2 THEN ''Second'' WHEN 4 THEN ''Third'' WHEN 8 THEN ''Fourth'' WHEN 16 THEN ''Last'' END + CASE s.freq_interval WHEN 1 THEN '' Sunday'' WHEN 2 THEN '' Monday'' WHEN 3 THEN '' Tuesday'' WHEN 4 THEN '' Wednesday'' WHEN 5 THEN '' Thursday'' WHEN 6 THEN '' Friday'' WHEN 7 THEN '' Saturday'' WHEN 8 THEN '' Day'' WHEN 9 THEN '' Weekday'' WHEN 10 THEN '' Weekend Day'' END + '' of every '' + convert(varchar,s.freq_recurrence_factor) + '' month(s)'' END AS Occurs_detail , CASE s.freq_subday_type WHEN 1 THEN ''Occurs once at '' + master.dbo.fn_Time2Str(s.active_start_time) WHEN 2 THEN ''Occurs every '' + convert(varchar,s.freq_subday_interval) + '' Seconds(s) Starting at '' + master.dbo.fn_Time2Str(s.active_start_time) + '' ending at '' + master.dbo.fn_Time2Str(s.active_end_time) WHEN 4 THEN ''Occurs every '' + convert(varchar,s.freq_subday_interval) + '' Minute(s) Starting at '' + master.dbo.fn_Time2Str(s.active_start_time) + '' ending at '' + master.dbo.fn_Time2Str(s.active_end_time) WHEN 8 THEN ''Occurs every '' + convert(varchar,s.freq_subday_interval) + '' Hour(s) Starting at '' + master.dbo.fn_Time2Str(s.active_start_time) + '' ending at '' + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency , CASE WHEN s.freq_type = 1 THEN ''On date: '' + master.dbo.fn_Date2Str(s.active_start_date) + '' At time: '' + master.dbo.fn_Time2Str(s.active_start_time) WHEN s.freq_type < 64 THEN ''Start date: '' + master.dbo.fn_Date2Str(s.active_start_date) + '' end date: '' + master.dbo.fn_Date2Str(s.active_end_date) END as Duration , master.dbo.fn_Date2Str(xp.next_run_date) + '' '' + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date FROM msdb.dbo.sysjobs j (NOLOCK) INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id WHERE 1 = 1 @Filter ORDER BY j.name' IF @Filter = 'Y' SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ') ELSE IF @Filter = 'N' SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ') ELSE IF @Filter = 'X' SET @sql = REPLACE(@sql,'@Filter', 'AND s.active_end_date < convert(varchar(8),GetDate(),112) ') ELSE SET @sql = REPLACE(@sql,'@Filter','') EXEC(@sql)

Below sample result of above script