By Richie Lee

Hello!

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.

Happy scripting!


/****** 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'''+@JobName+''''+
',@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'''+@JobName+''' ;'
WHEN @JobRunning = 0 AND @NewJobStatus = 1 THEN 'EXEC msdb.dbo.sp_start_job N'''+@JobName+''' ;'
END
PRINT @FeedBack
exec sp_executesql @SQL

GO