How to: Disable or Enable a SQL Server Job Programatically

How to: Disable or Enable a SQL Server Job

Step 1: Find the JobId 

SELECT
job_id,name,description,enabled
FROM
msdb.dbo.sysjobs
ORDER BY
name ASC

Step 2: Update the enabled flag

— Disable SQL Job

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 0

— Enable SQL Job

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 1

 

Consider:

Create a cursor to loop through all SQL Jobs whenever you want to quickly enable/disable SQL Jobs

Consider creating a log of what jobs are enabled on a given day via bcp or SELECT INTO. This way you can store a log of what jobs are active on a given day, and roll back to that state when necessary.

 

DECLARE @sql NVARCHAR(4000)
DECLARE @cmd NVARCHAR(4000)
DECLARE @timestamp VARCHAR(50)
SELECT @timestamp = CAST(datediff(s,'1970-01-01',getutcdate()) AS VARCHAR)

SET @sql = 'SELECT job_id,name,description,enabled INTO dbo.sysjobs_'+ @timestamp + ' FROM msdb.dbo.sysjobs ORDER BY name ASC ' 
PRINT @sql 

-- EXEC sp_executesql @sql 

 

 

SET @sql = 'SELECT job_id,name,description,enabled FROM msdb.dbo.sysjobs ORDER BY name ASC ' 
SET @cmd = 'bcp "'+@sql + '" out "C:\cya\sysjobs.'+@timestamp+'.dat" -n -S server_name -T'
PRINT @cmd

-- EXEC master..xp_cmdshell @cmd 

 

I had one specific job which I wanted to be able to enable/disable during testing. So i created a batch file that accepts 3 parameters

  • action
  • username
  • password

 
Create a batch file called myjobmanager.bat as follows:


REM ## EXPECTS PARAMETERS -- 1. ENABLE/DISABLE 2. Username 3. Password
osql -S servername -d msdb -i %1_myjob.sql -U %2 -P %3 -n >> %1_myjob.log

Create 2 script files

—- disable_myjob.sql —-

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
-- DISABLE JOB
EXEC sp_update_job @job_id = @job_id , @enabled = 0

—- enable_myjob.sql —-

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
-- ENABLE JOB
EXEC sp_update_job @job_id = @job_id , @enabled = 1

To enable or disable the job run the script from the command line.

myjobmanager.bat enable username password

myjobmanager.bat disable username password

 

Of course you can then extend this to better suit your own uses by using SQLCMD and more substitution paramaters, such as dynamically putting server name, jobid, or job name….

 

For more ideas, go into your database and look into what other parameters you may want to change. Possibilities are endless. Change the name of the job, the owner, etc.

EXEC msdb.dbo.sp_helptext 'sp_update_job'

2 thoughts on “How to: Disable or Enable a SQL Server Job Programatically

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