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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools