Share via

範例：使用 WMI 提供者建立 SQL Server Agent 警示

  • 發行項

適用於：SQL Server

使用 WMI 事件提供者的一個常見方式是建立回應特定事件的 SQL Server Agent 警示。 下列範例提供簡單的警示，可儲存數據表中的 XML 死結圖形事件，以供稍後分析。 SQL Server Agent 會提交 WQL 要求、接收 WMI 事件，以及執行作業以回應事件。 請注意，雖然有數個 Service Broker 物件涉及處理通知訊息，但 WMI 事件提供者會處理建立和管理這些物件的詳細數據。

範例

首先，會在資料庫中建立 AdventureWorks2022 數據表來保存死結圖形事件。 數據表包含兩個數據行：數據 AlertTime 行會保存警示執行的時間，而數據 DeadlockGraph 行會保存包含死結圖形的 XML 檔。

然後，就會建立警示。 腳本會先建立警示將執行的作業、將作業步驟新增至作業，並將作業設為目前 SQL Server 實例的目標。 然後腳本會建立警示。

作業步驟會 TextData 擷取 WMI 事件實例的 屬性，並將該值 DeadlockGraph 插入數據表的數據 DeadlockEvents 行中。 SQL Server 會隱含地將字串轉換成 XML 格式。 因為作業步驟使用 Transact-SQL 子系統，因此作業步驟不會指定 Proxy。

每當記錄死結圖形追蹤事件時，警示就會執行作業。 針對 WMI 警示，SQL Server Agent 會使用指定的命名空間和 WQL 語句來建立通知查詢。 針對此警示，SQL Server Agent 會監視本機電腦上的預設實例。 WQL 語句會要求 DEADLOCK_GRAPH 預設實例中的任何事件。 若要變更警示所監視的實例，請將 中的@wmi_namespace實例名稱MSSQLSERVER取代為 警示。

注意

若要讓 SQL Server Agent 接收 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 Agent 啟動警示並執行作業。 執行下列腳本來檢查資料表的內容 DeadlockEvents ：

SELECT * FROM DeadlockEvents;
GO

數據 DeadlockGraph 行應該包含 XML 檔，其中顯示死結圖形事件的所有屬性。

相關內容