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
|
