Skip to content

Commit fe3e899

Browse files
rwestMSFTrwestMSFT
committed
Refresh WMI provider events and include issue 9435
1 parent 8151544 commit fe3e899

12 files changed

Lines changed: 567 additions & 490 deletions

‎docs/relational-databases/wmi-provider-server-events/media/sql-wmi-ddl-events-ktm.gif‎

-88.2 KB
Binary file not shown.

‎docs/relational-databases/wmi-provider-server-events/media/sql-wmi-trc-all-events.gif‎

-34.8 KB
Binary file not shown.

‎docs/relational-databases/wmi-provider-server-events/media/understanding-the-wmi-provider-for-server-events/wmi-provider-functional-spec.png‎

10.6 KB
Loading

‎docs/relational-databases/wmi-provider-server-events/media/wmi-provider-functional-spec.gif‎

-17 KB
Binary file not shown.

‎docs/relational-databases/wmi-provider-server-events/sample-creating-a-sql-server-agent-alert-with-the-wmi-provider.md‎

Lines changed: 129 additions & 117 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@ title: "Create a SQL Server Agent Alert with the WMI Provider"
33
description: Create a SQL Server Agent alert that responds to specific events. This simple alert saves XML deadlock graph events in a table for later analysis.
44
author: markingmyname
55
ms.author: maghan
6-
ms.date: "03/14/2017"
6+
ms.reviewer: randolphwest
7+
ms.date: 10/30/2023
78
ms.service: sql
89
ms.subservice: wmi
910
ms.topic: "reference"
@@ -12,121 +13,132 @@ helpviewer_keywords:
1213
- "WMI Provider for Server Events, samples"
1314
- "sample applications [WMI]"
1415
---
15-
# Sample: Creating a SQL Server Agent Alert with the WMI Provider
16+
# Sample: Create a SQL Server Agent Alert with the WMI Provider
17+
1618
[!INCLUDE [SQL Server](../../includes/applies-to-version/sqlserver.md)]
17-
One common way to use the WMI Event Provider is to create SQL Server Agent alerts that respond to specific events. The following sample presents a simple alert that saves XML deadlock graph events in a table for later analysis. SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event. Notice that, although several Service Broker objects are involved in processing the notification message, the WMI Event Provider handles the details of creating and managing these objects.
18-
19-
## Example
20-
First, a table is created in the `AdventureWorks` database to hold the deadlock graph event. The table contains two columns: The `AlertTime` column holds the time that the alert runs, and the `DeadlockGraph` column holds the XML document that contains the deadlock graph.
21-
22-
Then, the alert is created. The script first creates the job that the alert will run, adds a job step to the job, and targets the job to the current instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. The script then creates the alert.
23-
24-
The job step retrieves the **TextData** property of the WMI event instance and inserts that value into the **DeadlockGraph** column of the **DeadlockEvents** table. Notice that [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] implicitly converts the string into XML format. Because the job step uses the [!INCLUDE[tsql](../../includes/tsql-md.md)] subsystem, the job step does not specify a proxy.
25-
26-
The alert runs the job whenever a deadlock graph trace event would be logged. For a WMI alert, SQL Server Agent creates a notification query using the namespace and WQL statement specified. For this alert, SQL Server Agent monitors the default instance on the local computer. The WQL statement requests any `DEADLOCK_GRAPH` event in the default instance. To change the instance that the alert monitors, substitute the instance name for `MSSQLSERVER` in the `@wmi_namespace` for the alert.
27-
19+
20+
One common way to use the WMI Event Provider is to create SQL Server Agent alerts that respond to specific events. The following sample presents a simple alert that saves XML deadlock graph events in a table for later analysis. SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event. Notice that, although several Service Broker objects are involved in processing the notification message, the WMI Event Provider handles the details of creating and managing these objects.
21+
22+
## Example
23+
24+
First, a table is created in the `AdventureWorks2022` database to hold the deadlock graph event. The table contains two columns: The `AlertTime` column holds the time that the alert runs, and the `DeadlockGraph` column holds the XML document that contains the deadlock graph.
25+
26+
Then, the alert is created. The script first creates the job that the alert will run, adds a job step to the job, and targets the job to the current instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)]. The script then creates the alert.
27+
28+
The job step retrieves the `TextData` property of the WMI event instance and inserts that value into the `DeadlockGraph` column of the `DeadlockEvents` table. [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] implicitly converts the string into XML format. Because the job step uses the [!INCLUDE [tsql](../../includes/tsql-md.md)] subsystem, the job step doesn't specify a proxy.
29+
30+
The alert runs the job whenever a deadlock graph trace event would be logged. For a WMI alert, SQL Server Agent creates a notification query using the namespace and WQL statement specified. For this alert, SQL Server Agent monitors the default instance on the local computer. The WQL statement requests any `DEADLOCK_GRAPH` event in the default instance. To change the instance that the alert monitors, substitute the instance name for `MSSQLSERVER` in the `@wmi_namespace` for the alert.
31+
2832
> [!NOTE]
29-
> For SQL Server Agent to receive WMI events, [!INCLUDE[ssSB](../../includes/sssb-md.md)] must be enabled in **msdb** and [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)].
30-
31-
```
32-
USE AdventureWorks ;
33-
GO
34-
35-
IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
36-
BEGIN
37-
DROP TABLE DeadlockEvents ;
38-
END ;
39-
GO
40-
41-
CREATE TABLE DeadlockEvents
42-
(AlertTime DATETIME, DeadlockGraph XML) ;
43-
GO
44-
-- Add a job for the alert to run.
45-
46-
EXEC msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
47-
@enabled=1,
48-
@description=N'Job for responding to DEADLOCK_GRAPH events' ;
49-
GO
50-
51-
-- Add a jobstep that inserts the current time and the deadlock graph into
52-
-- the DeadlockEvents table.
53-
54-
EXEC msdb.dbo.sp_add_jobstep
55-
@job_name = N'Capture Deadlock Graph',
56-
@step_name=N'Insert graph into LogEvents',
57-
@step_id=1,
58-
@on_success_action=1,
59-
@on_fail_action=2,
60-
@subsystem=N'TSQL',
61-
@command= N'INSERT INTO DeadlockEvents
62-
(AlertTime, DeadlockGraph)
63-
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
64-
@database_name=N'AdventureWorks' ;
65-
GO
66-
67-
-- Set the job server for the job to the current instance of SQL Server.
68-
69-
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ;
70-
GO
71-
72-
-- Add an alert that responds to all DEADLOCK_GRAPH events for
73-
-- the default instance. To monitor deadlocks for a different instance,
74-
-- change MSSQLSERVER to the name of the instance.
75-
76-
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
77-
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
78-
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
79-
@job_name='Capture Deadlock Graph' ;
80-
GO
81-
```
82-
83-
## Testing the Sample
84-
To see the job run, provoke a deadlock. In [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)], open two **SQL Query** tabs and connect both queries to the same instance. Run the following script in one of the query tabs. This script produces one result set and finishes.
85-
86-
```
87-
USE AdventureWorks ;
88-
GO
89-
90-
BEGIN TRANSACTION ;
91-
GO
92-
93-
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
94-
GO
95-
```
96-
97-
Run the following script in the second query tab. This script produces one result set and then blocks, waiting to acquire a lock on `Production.Product`.
98-
99-
```
100-
USE AdventureWorks ;
101-
GO
102-
103-
BEGIN TRANSACTION ;
104-
GO
105-
106-
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
107-
GO
108-
109-
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
110-
GO
111-
```
112-
113-
Run the following script in the first query tab. This script blocks, waiting to acquire a lock on `Production.Location`. After a short time-out, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] will choose either this script or the script in the sample as the deadlock victim and end the transaction.
114-
115-
```
116-
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
117-
GO
118-
```
119-
120-
After provoking the deadlock, wait several moments for SQL Server Agent to activate the alert and run the job. Examine the contents of the `DeadlockEvents` table by running the following script:
121-
122-
```
123-
SELECT * FROM DeadlockEvents ;
124-
GO
125-
```
126-
127-
The `DeadlockGraph` column should contain an XML document that shows all the properties of the deadlock graph event.
128-
129-
## See Also
130-
[WMI Provider for Server Events Concepts](../../relational-databases/wmi-provider-server-events/wmi-provider-for-server-events-concepts.md)
131-
132-
33+
> For SQL Server Agent to receive WMI events, [!INCLUDE [ssSB](../../includes/sssb-md.md)] must be enabled in `msdb` and [!INCLUDE [ssSampleDBobject](../../includes/sssampledbobject-md.md)].
34+
35+
```sql
36+
USE AdventureWorks2022;
37+
GO
38+
39+
IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
40+
DROP TABLE DeadlockEvents;
41+
GO
42+
43+
CREATE TABLE DeadlockEvents (
44+
AlertTime DATETIME,
45+
DeadlockGraph XML
46+
);
47+
GO
48+
```
49+
50+
Add a job for the alert to run.
51+
52+
```sql
53+
EXEC msdb.dbo.sp_add_job @job_name = N'Capture Deadlock Graph',
54+
@enabled = 1,
55+
@description = N'Job for responding to DEADLOCK_GRAPH events';
56+
GO
57+
```
58+
59+
Add a job step that inserts the current time and the deadlock graph into the `DeadlockEvents` table.
60+
61+
```sql
62+
EXEC msdb.dbo.sp_add_jobstep @job_name = N'Capture Deadlock Graph',
63+
@step_name = N'Insert graph into LogEvents',
64+
@step_id = 1,
65+
@on_success_action = 1,
66+
@on_fail_action = 2,
67+
@subsystem = N'TSQL',
68+
@command = N'INSERT INTO DeadlockEvents
69+
(AlertTime, DeadlockGraph)
70+
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
71+
@database_name = N'AdventureWorks2022';
72+
GO
73+
```
74+
75+
Set the job server for the job to the current instance of [!INCLUDE [ssnoversion-md](../../includes/ssnoversion-md.md)].
76+
77+
```sql
78+
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph';
79+
GO
80+
```
81+
82+
Add an alert that responds to all `DEADLOCK_GRAPH` events for the default instance. To monitor deadlocks for a different instance, change `MSSQLSERVER` to the name of the instance.
83+
84+
```sql
85+
EXEC msdb.dbo.sp_add_alert @name = N'Respond to DEADLOCK_GRAPH',
86+
@wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
87+
@wmi_query = N'SELECT * FROM DEADLOCK_GRAPH',
88+
@job_name = 'Capture Deadlock Graph';
89+
GO
90+
```
91+
92+
## Test the sample
93+
94+
To see the job run, provoke a deadlock. In [!INCLUDE [ssManStudioFull](../../includes/ssmanstudiofull-md.md)], open two **SQL Query** tabs and connect both queries to the same instance. Run the following script in one of the query tabs. This script produces one result set and finishes.
95+
96+
```sql
97+
USE AdventureWorks2022;
98+
GO
99+
100+
BEGIN TRANSACTION;
101+
GO
102+
103+
SELECT TOP (1) Name
104+
FROM Production.Product WITH (XLOCK);
105+
GO
106+
```
107+
108+
Run the following script in the second query tab. This script produces one result set and then blocks, waiting to acquire a lock on `Production.Product`.
109+
110+
```sql
111+
USE AdventureWorks2022;
112+
GO
113+
114+
BEGIN TRANSACTION;
115+
GO
116+
117+
SELECT TOP (1) Name
118+
FROM Production.Location WITH (XLOCK);
119+
GO
120+
121+
SELECT TOP (1) Name
122+
FROM Production.Product WITH (XLOCK);
123+
GO
124+
```
125+
126+
Run the following script in the first query tab. This script blocks, waiting to acquire a lock on `Production.Location`. After a short time-out, [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] will choose either this script or the script in the sample as the deadlock victim and end the transaction.
127+
128+
```sql
129+
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK);
130+
GO
131+
```
132+
133+
After provoking the deadlock, wait several moments for SQL Server Agent to activate the alert and run the job. Examine the contents of the `DeadlockEvents` table by running the following script:
134+
135+
```sql
136+
SELECT * FROM DeadlockEvents;
137+
GO
138+
```
139+
140+
The `DeadlockGraph` column should contain an XML document that shows all the properties of the deadlock graph event.
141+
142+
## Related content
143+
144+
- [WMI Provider for Server Events concepts](wmi-provider-for-server-events-concepts.md)

‎docs/relational-databases/wmi-provider-server-events/sample-using-the-wmi-event-provider-with-the-net-framework.md‎

Lines changed: 62 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@ title: "Sample: Use the WMI Event Provider in .NET"
33
description: "A sample C# application uses the WMI Event Provider to return event data for all data definition language events that occur on an instance of SQL Server."
44
author: markingmyname
55
ms.author: maghan
6-
ms.date: "03/14/2017"
6+
ms.reviewer: randolphwest
7+
ms.date: 10/30/2023
78
ms.service: sql
89
ms.subservice: wmi
910
ms.topic: "reference"
@@ -12,62 +13,64 @@ helpviewer_keywords:
1213
- "sample applications [WMI]"
1314
- "managed code [WMI]"
1415
---
15-
# Sample: Using the WMI Event Provider with the .NET Framework
16+
# Sample: Use the WMI Event Provider with the .NET Framework
17+
1618
[!INCLUDE [SQL Server](../../includes/applies-to-version/sqlserver.md)]
17-
The following sample creates an application in C# that uses the WMI Event Provider to return event data for all data definition language (DDL) events that occur on a default installation instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].
18-
19-
## Example
20-
The example compiles by using the following command file:
21-
22-
```
23-
set compiler_path=C:\WINNT\Microsoft.NET\Framework\v2.0.50110
24-
25-
%compiler_path%\csc %1
26-
```
27-
28-
```
29-
using System;
30-
using System.Management;
31-
32-
class SQLWEPExample
33-
{
34-
public static void Main(string[] args)
35-
{
36-
string query = @"SELECT * FROM DDL_EVENTS " ;
37-
// Default namespace for default instance of SQL Server
38-
string managementPath =
39-
@"\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER";
40-
ManagementEventWatcher watcher =
41-
new ManagementEventWatcher(new WqlEventQuery (query));
42-
ManagementScope scope = new ManagementScope (managementPath);
43-
scope.Connect();
44-
watcher.Scope = scope;
45-
Console.WriteLine("Watching...");
46-
while (true)
47-
{
48-
ManagementBaseObject obj = watcher.WaitForNextEvent();
49-
Console.WriteLine("Event!");
50-
foreach (PropertyData data in obj.Properties)
51-
{
52-
Console.Write("{0}:", data.Name);
53-
if (data.Value == null)
54-
{
55-
Console.WriteLine("<null>");
56-
}
57-
else
58-
{
59-
Console.WriteLine(data.Value.ToString());
60-
}
61-
}
62-
Console.WriteLine("-----");
63-
Console.WriteLine();
64-
Console.WriteLine();
65-
}
66-
}
67-
}
68-
```
69-
70-
## See Also
71-
[WMI Provider for Server Events Concepts](../../relational-databases/wmi-provider-server-events/wmi-provider-for-server-events-concepts.md)
72-
73-
19+
20+
The following sample creates an application in C# that uses the WMI Event Provider to return event data for all data definition language (DDL) events that occur on a default installation instance of [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)].
21+
22+
## Examples
23+
24+
The example compiles by using the following command file:
25+
26+
```cmd
27+
set compiler_path=C:\WINNT\Microsoft.NET\Framework\v2.0.50110
28+
29+
%compiler_path%\csc %1
30+
```
31+
32+
```csharp
33+
using System;
34+
using System.Management;
35+
36+
class SQLWEPExample
37+
{
38+
public static void Main(string[] args)
39+
{
40+
string query = @"SELECT * FROM DDL_EVENTS " ;
41+
// Default namespace for default instance of SQL Server
42+
string managementPath =
43+
@"\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER";
44+
ManagementEventWatcher watcher =
45+
new ManagementEventWatcher(new WqlEventQuery (query));
46+
ManagementScope scope = new ManagementScope (managementPath);
47+
scope.Connect();
48+
watcher.Scope = scope;
49+
Console.WriteLine("Watching...");
50+
while (true)
51+
{
52+
ManagementBaseObject obj = watcher.WaitForNextEvent();
53+
Console.WriteLine("Event!");
54+
foreach (PropertyData data in obj.Properties)
55+
{
56+
Console.Write("{0}:", data.Name);
57+
if (data.Value == null)
58+
{
59+
Console.WriteLine("<null>");
60+
}
61+
else
62+
{
63+
Console.WriteLine(data.Value.ToString());
64+
}
65+
}
66+
Console.WriteLine("-----");
67+
Console.WriteLine();
68+
Console.WriteLine();
69+
}
70+
}
71+
}
72+
```
73+
74+
## Related content
75+
76+
- [WMI Provider for Server Events concepts](wmi-provider-for-server-events-concepts.md)

0 commit comments

Comments
 (0)