Purpose of the Article
This article is devised to give a way of auto disabling the SQL Server Agent jobs in case of failures. In some cases, it is required to disable the job if failed otherwise SQL Server Agent will keep trigger the job and job will keep processing the data, it may lead to huge data discrepancy and other issues like unnecessary failures notifications to the users etc.
How we can implement auto disable functionality of jobs in case of failures
1. Get list of all the jobs having status as failed using msdb.sysjobs and msdb.jobhistory tables.
2. Update the Enabled flag of the failed jobs to 0 in msdb.sysjobs table
3. There should a job e.g ‘Auto_Disable_Failed_Jobs’ which should run the T-SQL script for above 2 steps. Frequency of the job can be every 1 or 2 mins.
Steps to auto disable the Jobs in case of failures
1. Below is the script which gives the list of failed jobs as well as update statement to disable the failed jobs:
USE MSDB
GO
--Declare Table Variable
DECLARE @FailedJobs TABLE
(NAME VARCHAR(500),
[MESSAGE] VARCHAR(2000),
LASTRUNDATE VARCHAR(500),
LASTRUNTIME VARCHAR(500))
-- Getting Failed Jobs
INSERT INTO @FailedJobs
(NAME,[MESSAGE],LASTRUNDATE,LASTRUNTIME)
SELECT
J.[NAME] AS [NAME],
H.MESSAGE AS [MESSAGE],
H.RUN_DATE AS LASTRUNDATE,
H.RUN_TIME AS LASTRUNTIME
FROM SYSJOBHISTORY H
INNER JOIN SYSJOBS J
ON H.JOB_ID = J.JOB_ID
WHERE J.ENABLED = 1
AND H.INSTANCE_ID IN
(SELECT MAX(H.INSTANCE_ID)
FROM SYSJOBHISTORY H GROUP BY (H.JOB_ID))
AND H.RUN_STATUS = 0
--Disabling the Failed Jobs
UPDATE SJ
SET [enabled] = 0
FROM sysjobs SJ
INNER JOIN @FailedJobs F
ON SJ.NAME = F.NAME
2. Create a SQL Agent Job e.g Auto_Disable_Failed_Jobs and run the above code in the job step:
3. If we don’t want to check the failure status for all the jobs available in an instance of SQL Server then we can filter the job name within the script.
4. Once Job issue is resolved, System admin or DB admin / support team can enable the job manually.
Conclusion
By using the above mentioned steps, we can implement the auto disable functionality of failed jobs.
---------------------------- Author- Vishal Jharwade----------------------------------