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.
Explanation
Alter an Extended Events Session Using T-SQL
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:
- We can also DROP events, targets, etc…
- Only one type or action can be performed with each call to ALTER, e.g., 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 change 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 the “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
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017