Friday, July 14, 2023

SQL Server Agent - How to disable SQL Agent Job using T-SQL script

To do unattended tasks like if error comes need to disable the job. In this case, we need to disable the job through T-SQL script ony. We can use this script in Job's step, if any job step failed then this script wil disable the job. This is very useful in keeping database safe from unnecessary actions from SQL Agent Job etc.

USE MSDB;
GO

--Declaring Variable
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @Cnt INT
DECLARE @Min INT
--Declaring Table Variable
DECLARE @SQLAgentJob (
	Id INT IDENTITY
	,Job_Id UNIQUEIDENTIFIER
	)

-- Setting minimum value to 1
SET @Min = 1

--Inserting all the Job IDs which are enabled for my application MyAppProcessing
INSERT INTO @SQLAgentJob
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 1
AND Name LIKE 'MyAppProcessing%'

-- Count of total Job enabled
SELECT @Cnt = COUNT(Job_Id)
FROM @SQLAgentJob

--While Loop
WHILE @Min <= @Cnt
BEGIN
	SELECT @job_id = job_id
	FROM @SQLAgentJob
	WHERE ID = @Min

	EXEC msdb.dbo.sp_update_job @job_id = @job_id
		,@enabled = 0

	SET @Min = @Min + 1
END

No comments:

Post a Comment