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

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: