对警报线程池的警报线程_审核和警报SQL Server作业状态更改（启用或禁用）

对警报线程池的警报线程

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.

在本文中，我们将讨论如何使用T-SQL跟踪SQL Server中启用或禁用SQL作业。 具有Sysadmin角色的用户具有修改SQL Server中任何作业信息的默认权限。 如果用户不在此角色中并希望访问此活动，则需要在msdb数据库中为该用户提供SQLAgentOperatorRole 。

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:

SQL Server引入了SQL作业，这些作业也可以用作调度程序，以依次执行多个步骤并在指定的时间范围内重复执行。 SQL作业的主要目的是在预定的时间和间隔执行事件或任务。 作业步骤可以执行以下任何事件调控器：

• Audit
  审计
• Data Collector
  资料收集器
• Database Engine Tuning Advisor
  数据库引擎优化顾问
• Database Maintenance
  数据库维护
• Full-Text
  全文
• Jobs from MSX
  MSX的职位
• Log Shipping
  日志传送
• REPL-Alert Response
  REPL警报响应
• REPL-Checkup
  REPL检查
• REPL-Distribution
  REPL分布
• REPL-Distribution Cleanup
  REPL分发清理
• REPL-History Cleanup
  REPL历史记录清理
• Replication
  复写
• REPL-LogReader
  REPL-LogReader
• REPL-Merge
  合并
• REPL-QueueReader
  REPL-QueueReader
• REPL-Snapshot
  REPL快照
• REPL-Subscription Cleanup
  REPL订阅清除
• 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.

SQL作业定义了SQL Server代理执行的管理任务和指定的一系列操作。 定义的作业可以运行一次或多次，并且可以在sysjobactivity表中监视它的成功或失败。

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.

我们应该怎么做？ 我们可以通过在msdb.dbo.sysjobs表上创建AFTER TRIGGER来设置警报。 这将检测“ 启用作业状态”列中的任何更改，并将通过电子邮件发送消息给您的DBA团队。

Here is the T-SQL query to get a list of jobs with enabled/disabled status:

这是T-SQL查询，以获取具有启用/禁用状态的作业列表：

SELECT job_id, name, enabled 
FROM msdb.dbo.sysjobs

启用/禁用SQL作业 (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.

如前所述，禁用的作业将不会被删除； 它将保持不活动状态，并且不会在计划的时间执行。 如果重新启用该作业，它将在计划的时间和间隔继续执行。 因此，启用或禁用状态是SQL Server作业的活动或非活动状态，这些状态也可以使用SSMS和T-SQL命令进行更改。

使用SSMS (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:

要禁用作业，请转到SQL SERVER代理| 作业活动监视器。 右键单击作业名称，然后选择“ 禁用作业” ，如下所示：

使用T-SQL命令 (Using T-SQL command)

To disable or enable a job using T-SQL, use the following commands:

要使用T-SQL禁用或启用作业，请使用以下命令：

--Disable
exec msdb..sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0

 --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.

让我们谈谈用户定义SQL作业。 这些工作的目的主要是监视组织的数据和任务，执行事件等等。 SQL作业是根据组织的要求进行设计和定义的，它们包括在特定的预定时间或间隔执行的各种步骤。 这些用户定义SQL作业被广泛用于执行各种任务，例如数据校正，更新或数据同步。 在某些情况下，SQL作业还可以用于生成具有面向用户信息的警报或电子邮件。

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

通常，将启用这些SQL作业，但是可能有充分的理由暂时禁用它们。 如前所述，禁用作业后，其他用户不会获得任何信息。 也没有对任务状态更改的默认审核，因此无法知道哪个用户修改了作业。 这使管理SQL作业信息的历史记录变得困难，但是可以通过以下操作使它更容易

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.

生成发出电子邮件警报的触发器。 在这种情况下， EVENTDATA（）有助于获取触发器内部的事件信息，已登录的用户，T-SQL语句等。

触发 (Trigger)

Here’s how you can write the trigger:

这是编写触发器的方法：

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).

job_event表存储触发器内启用列的每个修改事件，而sysjobactivity表存储作业的每个修改。 在历史记录表中，只有最新记录作为在当前作业历史记录日志大小（以行为单位）参数（SQL Server代理|属性|历史记录）中定义的行大小值存在。

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.

如触发器中的@recipients以及表job_event所述 ，我们可以在SQL Server中访问SQL作业的此事件的审核历史记录。 此触发器将为个人或组生成电子邮件警报，这些警报在msdb.dbo.sp_send_dbmail过程的@recipients参数下提到。

翻译自: https://www.sqlshack.com/audit-and-alert-sql-server-jobs-status-change-enabled-or-disabled/

对警报线程池的警报线程