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.
SQL Server AGENT
I have collected some general facts related to SQL Server Agent and how you configure it. Hope it will help you.
SQL Server Agent is the service SQL Server uses to perform administrative tasks called jobs. Jobs are made up of one or more steps to accomplish the administrative task you need performed. Jobs can be executed on a recurring schedule and they have multiple methods to notify and record the results of the job.
Configuring SQL Server Agent
The SQL Server Agent service is installed once for each named instance of SQL Server installed on the server. For the default instance the service is called SQLSERVERAGENT. Named instances are called SQLAgent$instancename.
Service Accounts
SQL Server Agent runs as a service on the SQL Server and that service needs a user account to run under. The Agent service can run under a built-in account such as Network Service or System. It can also run under a local user account or domain account.
It is a best practice to use a low privileged domain account for the SQL Server Agent service to ensure it has access to only those resources it needs. This will also allow the Agent to connect to remote servers and access network resources you've granted permission to. Additionally, if your SQL Server is running in a cluster the SQL Server Agent service account MUST use a domain account, it cannot use a built-in or local account.
You'll be prompted to configure the SQL Agent user account during SQL Server installation. If you need to change the configuration after installation you can do this via SQL Server Configuration Manager.
Books Online has a complete description of the all issues you should consider before choosing an account for SQL Server Agent. The primary note about these considerations is all the built-in accounts have security vulnerabilities.
This is of particular importance because if your users are allowed to create their own jobs (executed under the SQL Agent account) your users may access secure resources via SQL Agent they would not normally have available to them. This is critically important to consider for your organization's data security particularly if you must comply with various regulations such asPCI, HIPAA, etc.
General Configuration Options
There are a few SQL Agent settings you can customize to meet your needs, some of the most common are listed below. Unless otherwise noted, these options can be configured via SSMS or using the msdb.dbo. sp_set_sqlagent_properties
system stored procedure.
Auto-Restart Services
The SQL Server and SQL Server Agent services monitor each other and restart each other if the other service fails. You can enable or disable the auto restart of either service. Note that these options should be disabled on clusters.
SQL Agent Log Files
After installation the SQL Agent log files will be configured to use the path %SQL Install Path%\MSSQL\LOG. If the log file location doesn't meet your needs you'll find the path is not configurable during installation and is not something you can modify in the GUI.
You can get instructions for moving the log file and a T-SQL script to reconfigure your log file path in my earlier post, Moving Default Directories in SQL Server. You will need to restart SQL Agent service for these changes to take effect.
You can also configure limits to the size of Agent log data and storage duration of Agent logs. You can limit the rows stored in the log both by total number of history rows and total number of rows per job. You can also restrict how much Agent history is kept by time period (e.g. last 30 days…)
