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:
- Audit
- Data Collector
- Database Engine Tuning Advisor
- Database Maintenance
- Full-Text
- Jobs from MSX
- Log Shipping
- REPL-Alert Response
- REPL-Checkup
- REPL-Distribution
- REPL-Distribution Cleanup
- REPL-History Cleanup
- Replication
- REPL-LogReader
- REPL-Merge
- REPL-QueueReader
- REPL-Snapshot
- 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:
|
1 2 |
SELECT job_id, name, enabled FROM msdb.dbo.sysjobs |
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.
Using SSMS
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:
|
1 2 |
--Disable exec msdb..sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0 |
|
1 2 |
--Enable 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.
Trigger
Here’s how you can write the trigger:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
ALTER TRIGGER [dbo].Alert_on_job_stat ON [dbo].[sysjobs] AFTER UPDATE AS BEGIN 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) BEGIN 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)) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Alert', @recipients = 'dba_alert@rdl.com', @subject = 'SQL job status Change Alert', @body = @body_content; END END |
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.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020