Search

Thursday, September 29, 2011

Disable a Job via TSQL


You can disable a Job either through Job ID or Job Name:
exec msdb..sp_update_job @job_id = 0xC0923E436928064EA33B46B2A47BFF61 , @enabled = 0


exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0
You can also manipulate via job category:
UPDATE j
SET j.Enabled = 0
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.syscategories c ON j.category_id = c.category_id
WHERE c.[Name] = 'Database Maintenance';
The other approach would be to add a step at the beginning of your other jobs which checks the status of the backup job and then either aborts or sleeps the current job if the backup is running.
Follow below procedure to Disable or Enable a SQL Server Job Programatically:
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

No comments:

Post a Comment