ALTER EVENT SESSION (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Starts or stops an event session or changes an event session configuration.

Transact-SQL syntax conventions

Syntax

ALTER EVENT SESSION event_session_name
ON { SERVER | DATABASE }
{
    [ [ {  <add_drop_event> [ , ...n ] }
       | { <add_drop_event_target> [ , ...n ] } ]
    [ WITH ( <event_session_options> [ , ...n ] ) ]
    ]
    | [ STATE = { START | STOP } ]
}

<add_drop_event>::=
{
    [ ADD EVENT <event_specifier>
         [ ( {
                 [ SET { event_customizable_attribute = <value> [ , ...n ] } ]
                 [ ACTION ( { [event_module_guid].event_package_name.action_name [ , ...n ] } ) ]
                 [ WHERE <predicate_expression> ]
        } ) ]
   ]
   | DROP EVENT <event_specifier> }

<event_specifier> ::=
{
[event_module_guid].event_package_name.event_name
}
<predicate_expression> ::=
{
    [ NOT ] <predicate_factor> | { ( <predicate_expression> ) }
    [ { AND | OR } [ NOT ] { <predicate_factor> | ( <predicate_expression> ) } ]
    [ , ...n ]
}

<predicate_factor>::=
{
    <predicate_leaf> | ( <predicate_expression> )
}

<predicate_leaf>::=
{
      <predicate_source_declaration> { = | < > | != | > | >= | < | <= } <value>
    | [event_module_guid].event_package_name.predicate_compare_name ( <predicate_source_declaration> , <value> )
}

<predicate_source_declaration>::=
{
    event_field_name | ( [event_module_guid].event_package_name.predicate_source_name )
}

<value>::=
{
    number | 'string'
}

<add_drop_event_target>::=
{
    ADD TARGET <event_target_specifier>
        [ ( SET { target_parameter_name = <value> [ , ...n ] } ) ]
    | DROP TARGET <event_target_specifier>
}

<event_target_specifier>::=
{
    [event_module_guid].event_package_name.target_name
}

<event_session_options>::=
{
    [       MAX_MEMORY = size [ KB | MB ] ]
    [ [ , ] EVENT_RETENTION_MODE = { ALLOW_SINGLE_EVENT_LOSS | ALLOW_MULTIPLE_EVENT_LOSS | NO_EVENT_LOSS } ]
    [ [ , ] MAX_DISPATCH_LATENCY = { seconds SECONDS | INFINITE } ]
    [ [ , ] MAX_EVENT_SIZE = size [ KB | MB ] ]
    [ [ , ] MEMORY_PARTITION_MODE = { NONE | PER_NODE | PER_CPU } ]
    [ [ , ] TRACK_CAUSALITY = { ON | OFF } ]
    [ [ , ] STARTUP_STATE = { ON | OFF } ]
    [ [ , ] MAX_DURATION = { <time duration> { SECONDS | MINUTES | HOURS | DAYS } | UNLIMITED } ]
}

Arguments

event_session_name

The name of an existing event session.

STATE = START | STOP

Starts or stops the event session. The STATE argument must be specified on its own. It can't be combined with any other arguments in the same ALTER EVENT SESSION statement.

ADD EVENT <event_specifier>

Identifies an event to associate with the event session. <event_specifier> is in the form of [event_module_guid].event_package_name.event_name, where:

• event_module_guid is the GUID for the module that contains the event.
• event_package_name is the package that contains the event.
• event_name is the name of the event.

Available events can be found by executing the following query:

SELECT o.name AS event_name,
       o.description AS event_description,
       p.name AS package_name,
       p.description AS package_description
FROM sys.dm_xe_objects AS o
     INNER JOIN sys.dm_xe_packages AS p
         ON o.package_guid = p.guid
WHERE o.object_type = 'event'
ORDER BY event_name ASC;

SET { event_customizable_attribute = <value> [ ,...n ] }

Customizable attributes for the event.

Customizable attributes for a given event can be found by executing the following query:

SELECT object_name,
       name AS column_name,
       type_name,
       column_value,
       description
FROM sys.dm_xe_object_columns
WHERE object_name = 'event-name-placeholder'
      AND column_type = 'customizable'
ORDER BY column_name ASC;

ACTION ( { [event_module_guid].event_package_name.action_name [ ,...n ] })

The action to associate with the event, where:

• event_module_guid is the GUID for the module that contains the action.
• event_package_name is the package that contains the action.
• action_name is the name of the action.

Available actions can be found by executing the following query:

SELECT o.name AS action_name,
       o.description AS action_description,
       p.name AS package_name,
       p.description AS package_description
FROM sys.dm_xe_objects AS o
     INNER JOIN sys.dm_xe_packages AS p
         ON o.package_guid = p.guid
WHERE o.object_type = 'action'
ORDER BY action_name ASC;

WHERE <predicate_expression>

Specifies the predicate expression used to determine if an event should be processed. If <predicate_expression> is true, the event is processed further by the actions and targets for the session. If <predicate_expression> is false, the event is dropped, avoiding additional action and target processing. Each predicate expression is limited to 3,000 characters.

event_field_name

The name of the event field that identifies the predicate source.

The fields for an event can be found by executing the following query:

SELECT oc.name AS field_name,
       oc.type_name AS field_type,
       oc.description AS field_description
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p
ON o.package_guid = p.guid
INNER JOIN sys.dm_xe_object_columns AS oc
ON o.name = oc.object_name
   AND
   o.package_guid = oc.object_package_guid
WHERE o.object_type = 'event'
      AND
      o.name = 'event-name-placeholder'
      AND
      oc.column_type = 'data'
ORDER BY field_name ASC;

[event_module_guid].event_package_name.predicate_source_name

The name of the global predicate source where:

• event_module_guid is the GUID for the module that contains the event.
• event_package_name is the package that contains the predicate source object.
• predicate_source_name is the name of the predicate source.

Predicate sources can be found by executing the following query:

SELECT o.name AS predicate_source_name,
       o.description AS predicate_source_description,
       p.name AS package_name,
       p.description AS package_description
FROM sys.dm_xe_objects AS o
     INNER JOIN sys.dm_xe_packages AS p
         ON o.package_guid = p.guid
WHERE o.object_type = 'pred_source'
ORDER BY predicate_source ASC;

[event_module_guid].event_package_name.predicate_compare_name

The name of the predicate comparator object, where:

• event_module_guid is the GUID for the module that contains the event.
• event_package_name is the package that contains the predicate comparator object.
• predicate_compare_name is the predicate comparator name.

Predicate comparators can be found by executing the following query:

SELECT o.name AS predicate_comparator_name,
       o.description AS predicate_comparator_description,
       p.name AS package_name,
       p.description AS package_description
FROM sys.dm_xe_objects AS o
     INNER JOIN sys.dm_xe_packages AS p
         ON o.package_guid = p.guid
WHERE o.object_type = 'pred_compare'
ORDER BY predicate_comparator ASC;

number

Any numeric type that can be represented as a 64-bit integer.

'string'

Either an ANSI or Unicode string as required by the predicate comparator. No implicit string type conversion is performed for the predicate compare functions. Passing the value of an unexpected type results in an error.

DROP EVENT <event_specifier>

Identifies an event to remove from the event session. Event specifier is in the form of [event_module_guid].event_package_name.event_name, where:

• event_module_guid is the GUID for the module that contains the event.

• event_package_name is the package that contains the action object.

• event_name is the event object.

  <event_specifier> must identify an event that was previously added to the event session.

ADD TARGET <event_target_specifier>

Identifies a target to associate with an event session. Event target specifier is in the form of [event_module_guid].event_package_name.target_name, where:

• event_module_guid is the GUID for the module that contains the event.
• event_package_name is the package that contains the action object.
• target_name is the name of the target.

Available targets can be found by executing the following query:

SELECT o.name AS target_name,
       o.description AS target_description,
       o.capabilities_desc,
       p.name AS package_name,
       p.description AS package_description
FROM sys.dm_xe_objects AS o
     INNER JOIN sys.dm_xe_packages AS p
         ON o.package_guid = p.guid
WHERE o.object_type = 'target'
ORDER BY target_name ASC;

An event session can have zero, one, or many targets. All targets added to an event session must be different. For example, you cannot add a second event_file target to a session that already has an event_file target.

For more information, including usage examples for commonly used targets, see Extended Events targets.

SET { target_parameter_name = <value> [ , ...n ] }

Sets a target parameter.

To see all target parameters and their descriptions, execute the following query, replacing target-name-placeholder with the target name, such as event_file, ring_buffer, histogram, etc.

SELECT name AS target_parameter_name,
       column_value AS default_value,
       description
FROM sys.dm_xe_object_columns
WHERE column_type = 'customizable'
      AND object_name = 'target-name-placeholder';

For more information about target types, see Extended Events targets.

DROP TARGET <event_target_specifier>

Identifies a target to remove from an event session. Event target specifier is in the form of [event_module_guid].event_package_name.target_name, where:

• event_module_guid is the GUID for the module that contains the event.
• event_package_name is the package that contains the action object.
• target_name is the name of the target.

Event target specifier must identify a target that was previously added to the event session.

WITH ( <event_session_options> [ ,...n ] )

Specifies the options to use with the event session.

MAX_MEMORY = size [ KB | MB ]

Specifies the maximum amount of memory to allocate to the session for event buffering. The default is 4 MB. size is a whole number and can be a kilobyte (KB) or a megabyte (MB) value. The maximum amount can't exceed 2 GB (2,048 MB). However, using memory values in GB range isn't recommended.

EVENT_RETENTION_MODE = { ALLOW_SINGLE_EVENT_LOSS | ALLOW_MULTIPLE_EVENT_LOSS | NO_EVENT_LOSS }

Specifies the event retention mode to use for handling event loss.

• ALLOW_SINGLE_EVENT_LOSS

  An event can be lost from the session. A single event is only dropped when all the event buffers are full. Losing a single event when event buffers are full minimizes the performance impact while also minimizing the loss of data in the processed event stream.

• ALLOW_MULTIPLE_EVENT_LOSS

  Full event buffers containing multiple events can be lost from the session. The number of events lost is dependent upon the memory size allocated to the session, the partitioning of the memory, and the size of the events in the buffer. This option generally avoids performance impact on the server when event buffers are quickly filled, but large numbers of events can be lost from the session.

• NO_EVENT_LOSS

  No event loss is allowed. This option ensures that all events raised are retained. Using this option forces all tasks that fire events to wait until space is available in an event buffer. Using NO_EVENT_LOSS can cause detectable performance issues while the event session is active. User sessions and queries might stall while waiting for events to be flushed from the buffer.

  Note

  For the event file targets in Azure SQL Database and in Azure SQL Managed Instance (with the SQL Server 2025 or Always-up-to-date update policy), starting from June 2024, NO_EVENT_LOSS behaves the same as ALLOW_SINGLE_EVENT_LOSS. If you specify NO_EVENT_LOSS, a warning with message ID 25665, severity 10, and message This target doesn't support the NO_EVENT_LOSS event retention mode. The ALLOW_SINGLE_EVENT_LOSS retention mode is used instead. is returned, and the session is created.

  This change avoids connection timeouts, failover delays, and other issues that can reduce database availability when NO_EVENT_LOSS is used with event file targets in Azure blob storage.

  NO_EVENT_LOSS is planned for removal as a supported EVENT_RETENTION_MODE argument in future updates to Azure SQL Database and Azure SQL Managed Instance. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

MAX_DISPATCH_LATENCY = { seconds SECONDS | INFINITE }

Specifies the amount of time that events are buffered in memory before being dispatched to event session targets. By default, this value is set to 30 seconds.

• seconds SECONDS

  The time, in seconds, to wait before starting to flush buffers to targets. seconds is a whole number. The minimum latency value is 1 second. However, 0 can be used to specify INFINITE latency.

• INFINITE

  Flush buffers to targets only when the buffers are full, or when the event session closes.

MAX_EVENT_SIZE = size [ KB | MB ]

Specifies the maximum allowable size for events. MAX_EVENT_SIZE should only be set to allow single events larger than MAX_MEMORY; setting it to less than MAX_MEMORY raises an error. size is a whole number and can be a kilobyte (KB) or a megabyte (MB) value. If size is specified in kilobytes, the minimum allowable size is 64 KB. When MAX_EVENT_SIZE is set, two buffers of size are created in addition to MAX_MEMORY, and the total memory used for event buffering is MAX_MEMORY + 2 * MAX_EVENT_SIZE.

MEMORY_PARTITION_MODE = { NONE | PER_NODE | PER_CPU }

Specifies the affinity of event buffers. Options other than NONE result in more buffers and higher memory consumption, but can avoid contention and improve performance on larger machines.

• NONE

  A single set of buffers are created within the Database Engine instance.

• PER_NODE

  A set of buffers is created for each NUMA node.

• PER_CPU

  A set of buffers is created for each CPU.

TRACK_CAUSALITY = { ON | OFF }

Specifies whether or not event causality is tracked. If enabled, causality allows related events on different server connections to be correlated together.

STARTUP_STATE = { ON | OFF }

Specifies whether or not to start this event session automatically when the Database Engine starts.

• ON

  The event session is started at startup.

• OFF

  The event session isn't started at startup.

MAX_DURATION = { time duration { SECONDS | MINUTES | HOURS | DAYS } | UNLIMITED }

Applies to: SQL Server 2025 (17.x) Preview

• UNLIMITED

  Causes an event session to run indefinitely once started, until stopped using the ALTER EVENT SESSION ... STATE = STOP statement.

• time duration SECONDS | MINUTES | HOURS | DAYS

  Causes an event session to stop automatically after the specified time elapses after the session start. The maximum supported duration is 2,147,483 seconds, or 35,792 minutes, or 596 hours, or 24 days.

For more information, see Time-bound event sessions.

Remarks

For more information about event session arguments, see Extended Events sessions.

The ADD and DROP arguments can't be used in the same statement.

Permissions

SQL Server and Azure SQL Managed Instance require the ALTER ANY EVENT SESSION permission.

Azure SQL Database requires the ALTER ANY DATABASE EVENT SESSION permission in the database.

Examples

A. Start and stop an event session

To use this example with database event sessions, replace ON SERVER with ON DATABASE.

ALTER EVENT SESSION test_session ON SERVER STATE = START;
ALTER EVENT SESSION test_session ON SERVER STATE = STOP;

B. Add new events to an existing session

To use this example with database event sessions, replace ON SERVER with ON DATABASE.

ALTER EVENT SESSION test_session ON SERVER
ADD EVENT sqlserver.database_transaction_begin,
ADD EVENT sqlserver.database_transaction_end;

C. View session statistics

To use this example with database event sessions, replace sys.dm_xe_sessions with sys.dm_xe_database_sessions, and sys.dm_xe_session_events with sys.dm_xe_database_session_events.

SELECT *
FROM sys.dm_xe_sessions
WHERE name = 'test_session';

SELECT se.*
FROM sys.dm_xe_session_events AS se
WHERE EXISTS (SELECT 1
              FROM sys.dm_xe_sessions AS s
              WHERE s.address = se.event_session_address
                    AND s.name = 'test_session');

Related content

• CREATE EVENT SESSION (Transact-SQL)
• DROP EVENT SESSION (Transact-SQL)
• SQL Server Extended Events Targets
• sys.server_event_sessions (Transact-SQL)
• sys.dm_xe_objects (Transact-SQL)
• sys.dm_xe_object_columns (Transact-SQL)