Alter SQL Server Extended Events Session



By:
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.

alter an extended events session

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.

alter an extended events session
Additional Information

Last Update: 6/14/2019






Comments For This Article





download








get free sql tips
agree to terms


Learn more about SQL Server tools