通过

示例：使用 WMI 提供程序创建 SQL Server 代理警报

  • 项目

适用范围：SQL Server

WMI 事件提供程序的一个常见用法是创建响应特定事件的 SQL Server 代理警报。 以下示例提供一个在表中保存 XML 死锁图形事件以供以后分析的简单警报。 SQL Server 代理提交 WQL 请求、接收 WMI 事件并运行作业以响应该事件。 请注意，尽管在处理通知消息中涉及几个 Service Broker 对象，WMI 事件提供程序将处理创建和管理这些对象的详细信息。

示例

首先，在 AdventureWorks2022 数据库中创建一个表来存放死锁图形事件。 该表包含两列：AlertTime 列存放警报运行的时间，DeadlockGraph 列则存放包含死锁图形的 XML 文档。

然后，创建警报。 该脚本首先创建警报将运行的作业，将作业步骤添加到作业，并将该作业定向到 SQL Server 的当前实例。 然后，脚本创建警报。

作业步骤检索 TextData WMI 事件实例的属性，并将该值 DeadlockGraph 插入到表的 DeadlockEvents 列中。 SQL Server 将字符串隐式转换为 XML 格式。 由于作业步骤使用 Transact-SQL 子系统，因此作业步骤不指定代理。

每当记录死锁图形跟踪事件时，警报都将运行该作业。 对于 WMI 警报，SQL Server 代理使用指定的命名空间和 WQL 语句创建一个通知查询。 对于此警报，SQL Server 代理监视本地计算机上的默认实例。 WQL 语句请求默认实例中的任何 DEADLOCK_GRAPH 事件。 若要更改警报监视的实例，请替换该警报的 MSSQLSERVER 中 @wmi_namespace 的实例名称。

注意

若要SQL Server 代理接收 WMI 事件，必须在和AdventureWorks2022中msdb启用 Service Broker。

USE AdventureWorks2022;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
DROP TABLE DeadlockEvents;
GO

CREATE TABLE DeadlockEvents (
    AlertTime DATETIME,
    DeadlockGraph XML
);
GO

为要运行的警报添加作业。

EXEC msdb.dbo.sp_add_job @job_name = N'Capture Deadlock Graph',
    @enabled = 1,
    @description = N'Job for responding to DEADLOCK_GRAPH events';
GO

添加将当前时间和死锁图插入表中的 DeadlockEvents 作业步骤。

EXEC msdb.dbo.sp_add_jobstep @job_name = N'Capture Deadlock Graph',
    @step_name = N'Insert graph into LogEvents',
    @step_id = 1,
    @on_success_action = 1,
    @on_fail_action = 2,
    @subsystem = N'TSQL',
    @command = N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
    @database_name = N'AdventureWorks2022';
GO

将作业的作业服务器设置为 SQL Server 的当前实例。

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph';
GO

添加一个警报，用于响应默认实例的所有 DEADLOCK_GRAPH 事件。 若要监视其他实例的死锁，请更改为 MSSQLSERVER 实例的名称。

EXEC msdb.dbo.sp_add_alert @name = N'Respond to DEADLOCK_GRAPH',
    @wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query = N'SELECT * FROM DEADLOCK_GRAPH',
    @job_name = 'Capture Deadlock Graph';
GO

测试示例

若要查看作业运行情况，请造成死锁。 在 SQL Server Management Studio 中，打开两个 SQL 查询 选项卡并将这两个查询连接到同一实例。 在其中一个查询选项卡中运行以下脚本。 此脚本生成一个结果集，然后结束。

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

SELECT TOP (1) Name
FROM Production.Product WITH (XLOCK);
GO

在第二个查询选项卡中运行以下脚本。此脚本生成一个结果集，然后阻止，等待获取锁定 Production.Product。

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

SELECT TOP (1) Name
FROM Production.Location WITH (XLOCK);
GO

SELECT TOP (1) Name
FROM Production.Product WITH (XLOCK);
GO

在第一个查询选项卡中运行以下脚本。此脚本阻止，等待获取锁定。Production.Location 超时后，SQL Server 会选择此脚本或示例中的脚本作为死锁受害者并结束事务。

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK);
GO

造成死锁后，等待一段时间，以便 SQL Server 代理激活警报并运行作业。 通过运行以下脚本查看 DeadlockEvents 表的内容：

SELECT * FROM DeadlockEvents;
GO

DeadlockGraph 列应包含显示死锁图形事件的所有属性的 XML 文档。

相关内容