In this article, we will talk about how to track enabled or disabled SQL jobs in SQL Server using T-SQL. Users with the Sysadmin role have the default permissions to modify the information of any jobs in SQL Server. If a user is not in this role and wants access to this activity, then the user needs to be given the SQLAgentOperatorRole in the msdb database.
SQL Server introduced SQL jobs that can also work as schedulers to perform multiple steps in sequence and repeat over a specified time frame. The main purpose of SQL jobs is to perform events or tasks at a predefined time and interval. Job steps can execute any of the following event governors:
- Data Collector
- Database Engine Tuning Advisor
- Database Maintenance
- Jobs from MSX
- Log Shipping
- REPL-Alert Response
- REPL-Distribution Cleanup
- REPL-History Cleanup
- REPL-Subscription Cleanup
- User Defined Jobs
According to its created definition, an established job administrates tasks or server events, so they occur regularly, and determines whether these tasks or events can be administered programmatically. This job can run on one local server or on multiple remote servers. Therefore, a task is a good candidate for automation if it involves a predictable sequence of steps and occurs at a specific time or in response to a specific event.
The SQL jobs define an administrative task and a specified series of actions that the SQL Server Agent performs. The defined job can be run one or more times and it can be monitored for success or failure in the sysjobactivity table.
When a job has been disabled, it is not deleted, but it can be enabled again when necessary. Unfortunately, there is no alert created when a job is disabled. This could occur by accident, and it could prove fatal, for example, if a backup job was accidentally disabled. Therefore, our goal is to create an alert on this event.
How can we do this? We can set up an alert by creating an AFTER TRIGGER ON msdb.dbo.sysjobs table. This will detect any change in job status enabled column and will email a message to your DBA team.
Here is the T-SQL query to get a list of jobs with enabled/disabled status:
SELECT job_id, name, enabled
Enable/Disable SQL job
As mentioned, a disabled job will not be deleted; it will remain in an inactive state and will not be executed at the scheduled time. If the job is re-enabled, it will continue to get executed at the scheduled time and interval. So, enable or disable stats are the Active or Inactive status of the SQL Server jobs and these statuses can be changed using SSMS and T-SQL command as well.
To disable a job, go to the SQL SERVER Agent | Job Activity Monitor. Right-click on the job name and choose Disable Job as shown below:
Using T-SQL command
To disable or enable a job using T-SQL, use the following commands:
exec msdb..sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0
exec msdb..sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 1
Let’s turn to user-defined SQL jobs. The purpose of these jobs is mainly to monitor the organization’s data and tasks, perform an event and more. SQL jobs are designed and defined according to the organization’s requirements, and they consist of various steps to perform at a certain scheduled time or interval. These user-defined SQL jobs are widely used to perform various tasks like Data Correction, Update or Data Synchronization. In some cases, SQL jobs can also be used to generate alerts or emails with the user-oriented information.
Usually, these SQL jobs will be enabled, but there may be a good reason for disabling them for a while. As mentioned, other users do not get any information when a job is disabled. There is also no default audit of changes to task status, so it is not possible to know which user modified the job. This makes it difficult to manage the history of SQL job information, but it can be made easier by
generating a trigger that issues an email alert. In this case, EVENTDATA() helps get event information inside the trigger, logged-in users, T-SQL statement and more.
Here’s how you can write the trigger:
ALTER TRIGGER [dbo].Alert_on_job_stat
DECLARE @old_status BIT, @new_status BIT, @job_name VARCHAR(1024)
SELECT @old_status = enabled FROM deleted
SELECT @new_status = enabled, @job_name = name FROM inserted
IF(@old_status <> @new_status)
INSERT INTO audit_db.dbo.job_event(login_name, job_name, status_, datetime)
VALUES(ORIGINAL_LOGIN(), @job_name, @new_status, GETDATE())
DECLARE @body_content VARCHAR(1024) = '';
SET @body_content = 'SQL job : ' + @job_name + ' has been ' +
CASE @new_status WHEN 1 THEN 'Enabled' ELSE 'Disabled' END
+ ' @ ' + CAST(GETDATE() AS VARCHAR(30))
@profile_name = 'SQL Alert',
@recipients = 'firstname.lastname@example.org',
@subject = 'SQL job status Change Alert',
@body = @body_content;
The job_event table stores each modification event of the enabled column inside the trigger while the sysjobactivity table stores each modification of jobs. Only the latest records will exist in the history table as row size value defined in the current job history log size (in rows) parameter (SQL Server Agent | Property | History).
As mentioned with @recipients in the trigger and with the help of the table job_event, we can access the audit history of this event in SQL Server for the SQL jobs. This trigger will generate email alerts to an individual or groups, which are mentioned under the @recipients parameter of the msdb.dbo.sp_send_dbmail procedure.
- Database Design and Logical Asseveration for SQL Query Optimization - March 11, 2020
- Monitor Transaction Log shipping using T-SQL and SSMS - March 10, 2020
- How to determine free space and file size for SQL Server databases - March 9, 2020