Export (0) Print
Expand All
2 out of 2 rated this helpful - Rate this topic

Manage Event Sessions in the Object Explorer

SQL Server 2012

This topic discusses the actions you can take in Object Explorer that affect Extended Events:

  • Create an Extended Events Session

  • Starting or Stopping an Extended Events Session

  • Export an Extended Events Session

  • Import an Extended Events Session Template

  • Edit an Extended Events Session

  • Delete an Extended Events Session

For more information about creating an Extended Events session, see Create an Extended Events Session.

You can start or stop an Extended Events session through the Query Editor using the ALTER EVENT SESSION statement, or by using the Extended Events node of Object Explorer.

When you stop an event session, the session is no longer listed as an active session in the sys.dm_xe_sessions dynamic management view (DMV). However, the session definition remains intact, and you can restart the session. To completely remove a session definition, you must delete the session.

To start or stop an Extended Events session, you must have the ALTER ANY EVENT SESSION permission.

When you stop a session that uses an in-memory target, such as the ring buffer, bucketing, event pairing, or synchronous event counter targets, all the information stored in the session's buffer (the target_data column of the sys.dm_xe_session_targets DMV) will be lost. To access event data after you stop the session, you should either save the data before you stop the session, or configure the session to use the file target.

Start or Stop an Extended Events Session Using Query Editor

To start a session, issue the following statements, replacing session_name with the name of the Extended Events session:

ALTER EVENT SESSION [session_name]
ON SERVER
STATE = START

To stop a session, issue the following statements, replacing session_name with the name of the Extended Events session:

ALTER EVENT SESSION [session_name]
ON SERVER
STATE = STOP

Start or Stop an Extended Events Session in Object Explorer

To start or stop an Extended Events session in Object Explorer, expand the Management, Extended Events, and then Sessions nodes and right click on a session and then click Start Session or Stop Session.

You can export an Extended Events session using Object Explorer, and save it as an .xml template file. For example, you may want to export a session and then apply the template to a new event session using the New Session Wizard or the New Session wizard.

When you export a session, make sure that you save the template file to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view the information.

To export an Extended Events session in Object Explorer:

  1. Expand the Management, Extended Events, and then Sessions nodes

  2. Right-click the session that you want to export, and select Export Session.

  3. In the Save As dialog box, select a location to save the file, type the file name in the File name box, and then click Save.

    If you save the file to the default SQL Server Management Studio template location, the template will appear in the dropdown list of predefined templates when you use the New Session Wizard and New Session dialog.

Using Object Explorer, you can import a template for an Extended Events session. For example, you may want to do this to create a session from a template that was exported from another instance of SQL Server.

To import an Extended Events session, you must have the necessary ALTER ANY EVENT SESSION permissions.

Before you import a template file, make sure that the file is from a trusted source. Template files should be saved to a location that uses the NTFS file system and where access is restricted to users who are authorized to view the information.

To import an Extended Events session:

  1. In Object Explorer, expand the Management, and then Extended Events nodes.

  2. Right-click Sessions and select New Session.

  3. Specify a name for the session.

  4. Expand the Template drop down box.

  5. Click <File From …>Open and browse for the session (XML file) you want to import.

The session appears under the Sessions node. By default, the session is not started.

You can edit an Extended Events session in Object Explorer.

To edit an Extended Events session:

  1. In Object Explorer, expand the Management, Extended Events, and then Sessions nodes.

  2. Right-click a session and select Properties.

  3. In the Select a page section, select the page or pages you want to edit.

  4. After you finish revising the event session, click OK.

Both the New Session Wizard and the New Session dialog have a Script option that generates the Transact-SQL that defines the Extended Events session.

You can access the Transact-SQL for an existing Extended Events session by right clicking the session name, selecting Script Session as, and then selecting Create to.

You can delete an Extended Events session:

  • In Query Editor using DROP EVENT SESSION.

  • In Object Explorer.

When you delete an event session, all configuration information is removed and the session definition no longer appears in the sys.server_event_sessions catalog view.

Note Note

system_health and AlwaysOn_health are included with SQL Server; do not delete them. system_health is enabled by default (for more information, see Use the system_health Session). AlwaysOn_health is off by default. These sessions collect data that can be useful for diagnosing performance issues.

To delete an Extended Events session, you must have the ALTER ANY EVENT SESSION permission.

To delete an Extended Events session in Object Explorer:

  1. Expand the Management, Extended Events, and then Sessions nodes.

  2. Right-click a session and select Delete.

  3. In the Delete Object dialog box, click OK.

  4. After you finish revising the event session, click OK.

To delete an Extended Events session in the Query Editor, Issue the following statements, replacing session_name with the name of the Extended Events session that you want to delete:

DROP EVENT SESSION [session_name]
ON SERVER
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.