As an environment grows bigger and you have many jobs across many server instances, it’s important to automate as much as you can. And starting with the small, tedious but time consuming jobs is never a bad idea! A good example is having to enable/disable SQL Agent jobs during maintenance, or when a snapshot for replication is being pushed out you may not want certain jobs to run to prevent failures. So here is a stored procedure to alter the status of a SQL Agent Job. You only need to pass in the name of the Agent job. If the job is enabled it will disable it, and as an added precaution it will stop the job if it is running. If the job is disabled it will enable and start it running. Though if you don’t want it to run then you can always add a bit switch to the sproc.
/****** Object: StoredProcedure [dbo].[Repl_Checks] Script Date: 08/07/2015 17:48:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Repl_Checks] @JobName NVARCHAR (100) AS DECLARE @OldJobStatus TINYINT DECLARE @NewJobStatus TINYINT DECLARE @SQL NVARCHAR (MAX) DECLARE @FeedBack NVARCHAR (256) DECLARE @JobRunning TINYINT = 0 SELECT @OldJobStatus = Enabled from MSDB.dbo.sysjobs WHERE [Name] = @JobName; SELECT @JobRunning = 1 FROM [msdb].[dbo].[sysjobactivity] ja LEFT JOIN [msdb].[dbo].[sysjobhistory] jh ON ja.job_history_id = jh.instance_id INNER JOIN [msdb].[dbo].[sysjobs] j ON ja.job_id = j.job_id WHERE ja.session_id = ( SELECT TOP 1 session_id FROM [msdb].[dbo].[syssessions] ORDER BY agent_start_date DESC ) AND start_execution_date IS NOT NULL AND stop_execution_date IS NULL AND j.NAME = @JobName SELECT @NewJobStatus = CASE WHEN @OldJobStatus = 1 THEN 0 ELSE 1 END, @FeedBack = CASE WHEN @NewJobStatus = 1 THEN 'Job has now been enabled. ' ELSE 'Job has now been disabled. ' END SET @SQL = 'EXEC msdb.dbo.sp_update_job @job_name = N'''[email protected]+''''+ ',@enabled = '+CAST(@NewJobStatus AS VARCHAR(1))+'' exec sp_executesql @SQL SELECT @SQL = CASE WHEN @JobRunning = 1 and @NewJobStatus = 0 THEN 'EXEC msdb.dbo.sp_stop_job N'''[email protected]+''' ;' WHEN @JobRunning = 0 AND @NewJobStatus = 1 THEN 'EXEC msdb.dbo.sp_start_job N'''[email protected]+''' ;' END PRINT @FeedBack exec sp_executesql @SQL GO