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

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

时间：2022-09-20 22:18:04

对警报线程池的警报线程

In this article, we will talk about how to track enabled or disabled SQL jobs in SQL Server using T-SQL. Users with theSysadminrole 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 theSQLAgentOperatorRolein themsdbdatabase.

在本文中，我们将讨论如何使用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 anAFTER TRIGGERONmsdb.dbo.sysjobstable. This will detect any change in job statusenabledcolumn 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 chooseDisable Jobas 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禁用或启用作业，请使用以下命令：

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

--Enableexec 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_statON[dbo].[sysjobs]AFTER UPDATEAS BEGINDECLARE @old_status BIT, @new_status BIT, @job_name VARCHAR(1024)SELECT @old_status = enabled FROM deletedSELECT @new_status = enabled, @job_name = name FROM insertedIF(@old_status <> @new_status)BEGININSERT 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@',@subject = 'SQL job status Change Alert',@body = @body_content;ENDEND

Thejob_eventtable 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 thecurrent job history log size (in rows)parameter (SQL Server Agent | Property | History).

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

As mentioned with@recipientsin the trigger and with the help of the tablejob_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@recipientsparameter of the msdb.dbo.sp_send_dbmail procedure.

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