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'

About these ads

I am a Database Consultant based in South Florida. I specialize in SQL Server Development and Administration. I have worked on projects including data archival, log shipping, SQL Server installation, performance tuning, database mirroring, disaster recovery and more recently data integration. Currently my role as a consultant has had me add focus to the particularly important subject of Documentation, Quality, Timeliness and Cross-Training within the realm of Technology Delivery.

Tagged with: , , , , , , , ,
Posted in database, dos, sql
One comment on “How to: Disable or Enable a SQL Server Job Programatically
  1. Lee says:

    Great script, I should try it and see if it works for me. thanks

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: