Alter SQL Server Extended Events Session

By: Ben Snaidero

Overview

Earlier in the tutorial we saw how we could use the ALTER EVENT SESSION command to start and stop a session.  We can also use this command to edit an extended events session. 

Alter an Extended Events Session Using TSQL

To modify an extended events session the command we use has the exact same syntax as the "CREATE EVENT SESSION" command with only the following differences.

1. We can also DROP events, targets, etc...
2. Only one type or action can be performed with each call to ALTER eg. Can't both DROP one event and ADD another in one call

Shown below are a few examples of how you can remove and add events as well as changing the target for a session.

ALTER EVENT SESSION [First_XEvent_Session] ON SERVER 
   DROP EVENT sqlserver.database_xml_deadlock_report;
ALTER EVENT SESSION [First_XEvent_Session] ON SERVER 
   DROP TARGET package0.ring_buffer;
ALTER EVENT SESSION [First_XEvent_Session] ON SERVER 
   ADD TARGET package0.event_file(SET filename=N'First_XEvent_Session');
ALTER EVENT SESSION [First_XEvent_Session] ON SERVER 
   ADD EVENT sqlserver.additional_memory_grant, ADD EVENT sqlserver.alter_table_update_data;

Alter an Extended Events Session Using SSMS

You can also edit an extended events session using SSMS.  If you expand the "Management" node in "Object Explorer", then expand "Extended Events", then "Sessions" and then right click on the name of the session you want to edit.  Once this menu opens you can select "Properties" item as shown below.

After selecting "Properties" the "Session Properties" dialog will open which has the exact same options as the "New Session" dialog we saw earlier.  Here you can add/drop events, predicates/filters, targets or edit any of the advanced options just as we did when creating a new session.

Additional Information

• Check out these other Extended Events articles