Snippet Name: Generate_Event_Session_Script
Tags:
Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:32:02
Dependencies:
DBA Notes:
Sample Output:
Title: Generate Event Session Script
Author: user
in SQL Server2019, I have a large number of extended events that I have created. using TSQL, can you help me script out the CREATE EVENT SESSION COMMANDs formt he metadata stored in sys.[server_event_sessions] and related tables?
Author: ChatGPT
To script out the `CREATE EVENT SESSION` commands for existing extended events sessions in SQL Server, you can query the metadata stored in system views like `sys.server_event_sessions`, `sys.server_event_session_events`, `sys.server_event_session_actions`, and `sys.server_event_session_targets`. Here's a script to generate the `CREATE EVENT SESSION` commands dynamically:
```sql
WITH EventSessions AS (
SELECT
s.name AS session_name,
s.startup_state,
s.max_memory,
s.max_dispatch_latency,
s.track_causality,
s.event_retention_mode_desc
FROM sys.server_event_sessions s
),
SessionEvents AS (
SELECT
ses.name AS session_name,
e.name AS event_name,
evs.predicate AS event_predicate
FROM sys.server_event_sessions ses
JOIN sys.server_event_session_events evs ON ses.event_session_id = evs.event_session_id
JOIN sys.server_event_session_events_internal evsi ON evs.event_id = evsi.event_id
JOIN sys.server_event_session_event_descriptions e ON evsi.event_id = e.event_id
),
SessionActions AS (
SELECT
ses.name AS session_name,
a.name AS action_name
FROM sys.server_event_sessions ses
JOIN sys.server_event_session_actions sa ON ses.event_session_id = sa.event_session_id
JOIN sys.server_event_session_actions_internal sai ON sa.action_id = sai.action_id
JOIN sys.server_event_session_action_descriptions a ON sai.action_id = a.action_id
),
SessionTargets AS (
SELECT
ses.name AS session_name,
t.name AS target_name,
stt.target_data AS target_options
FROM sys.server_event_sessions ses
JOIN sys.server_event_session_targets st ON ses.event_session_id = st.event_session_id
JOIN sys.server_event_session_target_internal stt ON st.target_id = stt.target_id
)
SELECT
'CREATE EVENT SESSION [' + es.session_name + '] ON SERVER ' + CHAR(10) +
(
SELECT STRING_AGG(
' ADD EVENT [' + se.event_name + '] (' + ISNULL(se.event_predicate, '') + ')',
',' + CHAR(10)
)
FROM SessionEvents se
WHERE se.session_name = es.session_name
) + CHAR(10) +
(
SELECT STRING_AGG(
' ADD TARGET [' + st.target_name + '] (' + ISNULL(st.target_options, '') + ')',
',' + CHAR(10)
)
FROM SessionTargets st
WHERE st.session_name = es.session_name
) + CHAR(10) +
'WITH (' + CHAR(10) +
' MAX_MEMORY=' + CAST(es.max_memory AS NVARCHAR) + ' KB, ' + CHAR(10) +
' EVENT_RETENTION_MODE=' + es.event_retention_mode_desc + ', ' + CHAR(10) +
' MAX_DISPATCH_LATENCY=' + CAST(es.max_dispatch_latency AS NVARCHAR) + ' SECONDS, ' + CHAR(10) +
' TRACK_CAUSALITY=' + IIF(es.track_causality = 1, 'ON', 'OFF') + ', ' + CHAR(10) +
' STARTUP_STATE=' + IIF(es.startup_state = 1, 'ON', 'OFF') + CHAR(10) +
')' AS create_session_script
FROM EventSessions es
ORDER BY es.session_name;
```
### Key Notes:
- This script uses Common Table Expressions (CTEs) to simplify the logic of retrieving session details, events, actions, and targets.
- It generates a complete `CREATE EVENT SESSION` command, including events, actions, targets, and options for each session.
- The script uses `STRING_AGG` to concatenate multiple events and targets for each session into a single script.
### Steps:
1. Run the script in your SQL Server Management Studio (SSMS).
2. The output will contain the `CREATE EVENT SESSION` commands for all the sessions defined in your server.
3. You can save or execute the output as needed.
Let me know if you'd like any refinements or further explanation!
Author: user
there are no system views related to sessions that end in _internal or _descriptions
Author: ChatGPT
You're correct that there are no system views with `_internal` or `_descriptions` suffixes in the context of Extended Events metadata. Let me adjust the script accordingly to use the actual system views available for Extended Events in SQL Server 2019.
Here???s the corrected script to generate the `CREATE EVENT SESSION` statements:
```sql
WITH EventSessions AS (
SELECT
es.name AS session_name,
es.startup_state,
es.max_memory,
es.max_dispatch_latency,
es.track_causality,
es.event_retention_mode_desc
FROM sys.server_event_sessions es
),
SessionEvents AS (
SELECT
es.name AS session_name,
ev.event_id,
ev.name AS event_name,
ses.predicate AS event_predicate
FROM sys.server_event_sessions es
JOIN sys.server_event_session_events ses ON es.event_session_id = ses.event_session_id
JOIN sys.event_session_events ev ON ses.event_id = ev.event_id
),
SessionActions AS (
SELECT
es.name AS session_name,
sa.action_id,
ac.name AS action_name
FROM sys.server_event_sessions es
JOIN sys.server_event_session_actions sa ON es.event_session_id = sa.event_session_id
JOIN sys.event_session_actions ac ON sa.action_id = ac.action_id
),
SessionTargets AS (
SELECT
es.name AS session_name,
st.target_id,
tg.name AS target_name,
st.target_data
FROM sys.server_event_sessions es
JOIN sys.server_event_session_targets st ON es.event_session_id = st.event_session_id
JOIN sys.event_session_targets tg ON st.target_id = tg.target_id
)
SELECT
'CREATE EVENT SESSION [' + es.session_name + '] ON SERVER ' + CHAR(10) +
(
SELECT STRING_AGG(
' ADD EVENT [' + se.event_name + '] (' + ISNULL(se.event_predicate, '') + ')',
',' + CHAR(10)
)
FROM SessionEvents se
WHERE se.session_name = es.session_name
) + CHAR(10) +
(
SELECT STRING_AGG(
' ADD TARGET [' + st.target_name + '] (' + ISNULL(st.target_data, '') + ')',
',' + CHAR(10)
)
FROM SessionTargets st
WHERE st.session_name = es.session_name
) + CHAR(10) +
'WITH (' + CHAR(10) +
' MAX_MEMORY=' + CAST(es.max_memory AS NVARCHAR) + ' KB, ' + CHAR(10) +
' EVENT_RETENTION_MODE=' + es.event_retention_mode_desc + ', ' + CHAR(10) +
' MAX_DISPATCH_LATENCY=' + CAST(es.max_dispatch_latency AS NVARCHAR) + ' SECONDS, ' + CHAR(10) +
' TRACK_CAUSALITY=' + IIF(es.track_causality = 1, 'ON', 'OFF') + ', ' + CHAR(10) +
' STARTUP_STATE=' + IIF(es.startup_state = 1, 'ON', 'OFF') + CHAR(10) +
')' AS create_session_script
FROM EventSessions es
ORDER BY es.session_name;
```
### Explanation of Changes:
1. **System Views**:
- Removed references to non-existent views like `_internal` or `_descriptions`.
- Used `sys.event_session_events`, `sys.event_session_actions`, and `sys.event_session_targets` for fetching event details.
2. **Adjusted Columns**:
- Replaced references to event/action IDs and names based on the correct system views.
3. **Simplified Logic**:
- Used straightforward joins and string aggregation with `STRING_AGG` to construct event and target definitions.
### Steps:
1. Execute the script in SSMS.
2. The result will generate a list of `CREATE EVENT SESSION` commands, one for each session defined on the server.
3. Copy and save these commands as needed for recreating the sessions.
Let me know if this resolves your needs or if further adjustments are required!
Author: user
I am scripting out the events and actions via a query i developed instead. however, when i script out the alwaysOn_health event, which i know has events, but is stopped on my non always on server, i get no events. if i use the GUI to script it, i get the events. what is wrong with the query below?
;WITH MyActions
AS
(
SELECT
ses.[event_session_id],
sa.event_id,
ses.name AS session_name,
sa.name AS action_name,
sa.package AS package
FROM sys.server_event_sessions ses
JOIN sys.server_event_session_actions sa ON ses.event_session_id = sa.event_session_id
)
SELECT DISTINCT
'ADD EVENT ' + evs.[package] + '.' + evs.[name] + '(' + CHAR(10)
' ACTION(' + sq.Columns +')' + CHAR(10)
ISNULL(' WHERE ' + evs.[predicate],'') + '),', t.session_name, t.[event_session_id], t.event_id, evs.*, sq.Columns FROM MyActions t JOIN ( SELECT [event_session_id], [event_id], Columns = STUFF((SELECT ',' + QUOTENAME(package) + '.' + QUOTENAME(action_name) FROM MyActions sc WHERE sc.[event_session_id] = s.[event_session_id] AND sc.event_id = s.event_id FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') FROM MyActions s ) sq ON t.[event_session_id] = sq.[event_session_id] AND t.event_id = sq.event_id JOIN sys.server_event_session_events evs ON t.event_session_id = evs.event_session_id AND t.event_id = evs.event_id --WHERE t.[schema_id] = SCHEMA_ID('lowell') WHERE [t].[session_name]='AlwaysOn_health'
Author: ChatGPT
The issue lies in the fact that the `AlwaysOn_health` event session is stopped on your non-Always On server. When an event session is stopped, its events and actions may not be actively retrieved by the queries against the system views. However, the GUI can script it out because it uses a deeper internal mechanism or accesses additional metadata, possibly from the default configuration of the `AlwaysOn_health` session.
Here are potential reasons and adjustments to address the issue:
### 1. **Stopped Session Limitation**:
- When an Extended Events session is stopped, SQL Server might not load some of its configuration into the relevant system views (`sys.server_event_session_events`, `sys.server_event_session_actions`, etc.).
- The `AlwaysOn_health` session is a system-defined session, which might have its metadata in a different internal store.
### 2. **Adjust Query to Include Default Configuration**:
- For system-defined sessions like `AlwaysOn_health`, check `sys.dm_xe_objects` and `sys.dm_xe_packages` for details on events and actions that might be tied to default settings.
Here???s an adjusted version of your query to handle this:
```sql
;WITH MyActions AS
(
SELECT
ses.[event_session_id],
sa.event_id,
ses.name AS session_name,
sa.name AS action_name,
sa.package AS package
FROM sys.server_event_sessions ses
LEFT JOIN sys.server_event_session_actions sa ON ses.event_session_id = sa.event_session_id
),
ActionColumns AS
(
SELECT
[event_session_id],
[event_id],
Columns = STUFF((
SELECT ',' + QUOTENAME(package) + '.' + QUOTENAME(action_name)
FROM MyActions sc
WHERE sc.[event_session_id] = s.[event_session_id]
AND sc.event_id = s.event_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 1, '')
FROM MyActions s
GROUP BY [event_session_id], [event_id]
)
SELECT DISTINCT
'ADD EVENT ' + evs.[package] + '.' + evs.[name] + '(' + CHAR(10) +
' ACTION(' + sq.Columns + ')' + CHAR(10) +
ISNULL(' WHERE ' + evs.[predicate], '') + '),',
t.session_name,
t.[event_session_id],
t.event_id,
evs.*,
sq.Columns
FROM MyActions t
LEFT JOIN ActionColumns sq
ON t.[event_session_id] = sq.[event_session_id]
AND t.event_id = sq.event_id
LEFT JOIN sys.server_event_session_events evs
ON t.event_session_id = evs.event_session_id
AND t.event_id = evs.event_id
WHERE t.[session_name] = 'AlwaysOn_health';
```
### Key Adjustments:
1. **`LEFT JOIN` instead of `JOIN`**:
- Use `LEFT JOIN` for `sys.server_event_session_actions` and `sys.server_event_session_events`. This ensures rows are included even if no actions or events are associated due to the session being stopped.
2. **Fallback to System Metadata**:
- If the query still fails to fetch events for `AlwaysOn_health`, query `sys.dm_xe_objects` for its default configuration. Example:
```sql
SELECT
xo.name AS event_name,
xp.name AS package_name
FROM sys.dm_xe_objects xo
JOIN sys.dm_xe_packages xp ON xo.package_guid = xp.guid
WHERE xo.object_type = 'event'
AND xo.name IN (
SELECT DISTINCT evs.name
FROM sys.server_event_session_events evs
WHERE evs.event_session_id IS NULL
);
```
3. **Check for Predicate or Package Absence**:
- Include a condition to handle cases where `evs.predicate` or `evs.package` is `NULL`.
4. **Validate Session State**:
- Confirm session state with `sys.dm_xe_sessions` to ensure it???s retrieving expected data.
### Debugging Steps:
- Check if `sys.server_event_session_events` returns any rows for `AlwaysOn_health` using a simplified query.
- Verify `AlwaysOn_health` session???s configuration in `sys.dm_xe_objects` for its default setup.
- Enable the session temporarily (`ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE = START;`) and recheck the query output.
These adjustments should help resolve discrepancies when querying the `AlwaysOn_health` session or similar system-defined sessions. Let me know how it works out!
Author: user
i have the opposite experience. i believe any dmv with dm_xe in the name, like dm_xe_sessions, are the sessions currently running
Author: ChatGPT
You're correct that DMVs with names like `dm_xe_sessions` primarily deal with **currently running sessions**, while metadata about all defined sessions (including stopped ones) is stored in views like `sys.server_event_sessions`. Let's refine the explanation and the query to handle your specific scenario better.
### Problem Recap
Your `AlwaysOn_health` session is **stopped**, and while its configuration exists in metadata (`sys.server_event_sessions`, `sys.server_event_session_events`, etc.), you aren't getting its associated events or actions. This happens because:
1. Events or actions may not be retrieved correctly if there are gaps in how `sys.server_event_session_events` or `sys.server_event_session_actions` is joined with other views.
2. `dm_xe_sessions` only works with **active sessions**, so it's not suitable for retrieving details of stopped sessions like `AlwaysOn_health`.
### Solution
Let???s focus on querying the `sys` catalog views, which hold metadata for all defined sessions???regardless of their state. Here???s the refined query:
```sql
;WITH MyActions AS
(
SELECT
ses.event_session_id,
ses.name AS session_name,
sa.event_id,
sa.name AS action_name,
sa.package AS package_name
FROM sys.server_event_sessions ses
LEFT JOIN sys.server_event_session_actions sa
ON ses.event_session_id = sa.event_session_id
),
ActionColumns AS
(
SELECT
event_session_id,
event_id,
Columns = STUFF((
SELECT ',' + QUOTENAME(package) + '.' + QUOTENAME(action_name)
FROM MyActions ma
WHERE ma.event_session_id = a.event_session_id
AND ma.event_id = a.event_id
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM MyActions a
GROUP BY event_session_id, event_id
)
SELECT DISTINCT
'ADD EVENT ' + evs.package + '.' + evs.name + '(' + CHAR(10) +
' ACTION(' + ISNULL(ac.Columns, '') + ')' + CHAR(10) +
ISNULL(' WHERE ' + evs.predicate, '') + '),',
ses.name AS session_name,
evs.*
FROM sys.server_event_sessions ses
LEFT JOIN sys.server_event_session_events evs
ON ses.event_session_id = evs.event_session_id
LEFT JOIN ActionColumns ac
ON evs.event_session_id = ac.event_session_id
AND evs.event_id = ac.event_id
WHERE ses.name = 'AlwaysOn_health';
```
### Key Adjustments
1. **Used `LEFT JOIN` for Actions and Events**:
- Ensures rows from `sys.server_event_sessions` are included even if there are no events or actions explicitly tied to the session due to its stopped state.
2. **Distinct Actions Handling**:
- Nested query aggregates actions using `FOR XML PATH('')` to create a comma-separated list of all actions for an event.
3. **Focus on `sys.server_event_sessions`**:
- Ensures that the script retrieves metadata even for stopped sessions, which aren't visible in `dm_xe_sessions` or similar DMVs.
4. **Predicate Inclusion**:
- Includes the event predicate if defined, defaulting to an empty string if `NULL`.
5. **State-Independent**:
- The query is state-agnostic and works for both running and stopped sessions.
### Why DMVs like `dm_xe_sessions` Aren???t Used
You're absolutely right that `dm_xe_sessions` focuses on running sessions. In contrast:
- **`sys.server_event_sessions`**: Contains metadata for all sessions (running or stopped).
- **`sys.server_event_session_events`**: Holds metadata for events within defined sessions.
- **`sys.server_event_session_actions`**: Stores associated actions, regardless of session state.
### Debugging Tips
1. Check if events and actions are correctly linked to `AlwaysOn_health` using:
```sql
SELECT *
FROM sys.server_event_sessions
WHERE name = 'AlwaysOn_health';
SELECT *
FROM sys.server_event_session_events
WHERE event_session_id = (
SELECT event_session_id
FROM sys.server_event_sessions
WHERE name = 'AlwaysOn_health'
);
SELECT *
FROM sys.server_event_session_actions
WHERE event_session_id = (
SELECT event_session_id
FROM sys.server_event_sessions
WHERE name = 'AlwaysOn_health'
);
```
2. If no rows are returned for events or actions, ensure they were defined for the session.
### When GUI Scripting Works
The GUI can fetch default configuration details for system-defined sessions (like `AlwaysOn_health`) even if the session has no active metadata. This is often due to hard-coded definitions or fallback mechanisms that aren't exposed in system views.
This query should provide the details you're looking for. Let me know if you run into any other issues!
Author: user
i see my issue is that the extended event has events, but no actions on the events
Author: ChatGPT
Thanks for clarifying! If your issue is that the Extended Event session has **events but no actions**, the query needs to handle scenarios where actions are absent, and still generate the correct `CREATE EVENT SESSION` script.
Here???s an updated query to properly script out `ADD EVENT` statements, whether or not actions are associated with the events:
### Updated Query
```sql
;WITH MyEvents AS
(
SELECT
ses.event_session_id,
ses.name AS session_name,
evs.event_id,
evs.name AS event_name,
evs.package AS event_package,
evs.predicate AS event_predicate
FROM sys.server_event_sessions ses
LEFT JOIN sys.server_event_session_events evs
ON ses.event_session_id = evs.event_session_id
),
MyActions AS
(
SELECT
ses.event_session_id,
sa.event_id,
sa.name AS action_name,
sa.package AS action_package
FROM sys.server_event_sessions ses
LEFT JOIN sys.server_event_session_actions sa
ON ses.event_session_id = sa.event_session_id
),
ActionColumns AS
(
SELECT
e.event_session_id,
e.event_id,
Columns = STUFF((
SELECT ',' + QUOTENAME(a.action_package) + '.' + QUOTENAME(a.action_name)
FROM MyActions a
WHERE a.event_session_id = e.event_session_id
AND a.event_id = e.event_id
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM MyEvents e
GROUP BY e.event_session_id, e.event_id
)
SELECT DISTINCT
'ADD EVENT ' + e.event_package + '.' + e.event_name + '(' + CHAR(10) +
CASE
WHEN ac.Columns IS NOT NULL THEN ' ACTION(' + ac.Columns + ')' + CHAR(10)
ELSE ''
END +
ISNULL(' WHERE ' + e.event_predicate, '') + '),',
e.session_name,
e.event_session_id,
e.event_id,
ac.Columns AS actions
FROM MyEvents e
LEFT JOIN ActionColumns ac
ON e.event_session_id = ac.event_session_id
AND e.event_id = ac.event_id
WHERE e.session_name = 'AlwaysOn_health';
```
### Key Adjustments
1. **Handle Events Without Actions**:
- Used a `CASE` statement to omit the `ACTION` clause if no actions are associated with an event.
2. **Predicate Handling**:
- Ensures the `WHERE` clause is included only if the event has a predicate defined.
3. **Event-Focused Logic**:
- Generates the `ADD EVENT` statement based solely on the events, while including actions only if they exist.
4. **LEFT JOIN for Actions**:
- Ensures that events without actions are still included in the results.
### Output Example
For a session like `AlwaysOn_health` with events but no actions, the query generates results similar to:
```sql
ADD EVENT sqlserver.error_reported(
WHERE severity >= 16
),
ADD EVENT sqlserver.lock_acquired(),
ADD EVENT sqlserver.lock_released(),
```
If actions exist for some events, they???ll be included:
```sql
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.client_hostname)
WHERE database_id = 5
),
```
### Validation
To verify:
1. Check the events for `AlwaysOn_health`:
```sql
SELECT *
FROM sys.server_event_session_events
WHERE event_session_id = (
SELECT event_session_id
FROM sys.server_event_sessions
WHERE name = 'AlwaysOn_health'
);
```
2. Ensure the query correctly includes all events, regardless of whether actions are defined.
Let me know if this resolves your issue or if further refinements are needed!
Author: user
that was an outstanding analysis and fix for my issue. thank you
Author: ChatGPT
You're most welcome, Lowell! ???? I'm thrilled that the analysis and fix addressed your issue effectively. Extended Events scripting can be tricky, especially when dealing with edge cases like events without actions, so it was my pleasure to help you tackle it.
If you need further assistance with SQL Server or anything else, feel free to reach out. Happy scripting and debugging! ????