DBA and SysAdmin World

April 27, 2009

Disable / Enable SQL Jobs by T-SQL

Filed under: Script — Tags: , — solihinho @ 12:23 AM
coding2 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).

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.



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

   DROP TABLE #sysjobs


Create a free website or blog at WordPress.com.