Altering SQL Job Status

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

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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