SQL Server 2012 has made working with Extended Events (XE) pretty simple when it comes to what sessions you have on your servers and what options you have selected and so forth but if you are like me then you still have some SQL Server instances that are 2008 or 2008 R2. For those servers there is no built-in way to view the Extended Event sessions in SSMS. I keep coming up against the same situations – Where are the xel log files? What events, actions or predicates are set for the events on the server? What sessions are there on the server already? I got tired of this being a perpetual question and wrote some TSQL to save as a snippet in SQL Prompt so that these details are permanently only a couple of clicks away.

First, some history. If you just came here for the code skip down a few paragraphs and it’s all there. If you want a little time to reminisce about SQL Server then stick with me through the next paragraph or two.

We are in a bit of a cross-over period currently, there are many versions of SQL Server but I would guess that SQL Server 2008, 2008 R2 and 2012 comprise the majority of installations. With each of these comes a set of management tools, of which SQL Server Management Studio (SSMS) is one. In 2008 and 2008 R2 Extended Events made their first appearance and there was no way to work with them in the SSMS interface. At some point the Extended Events guru Jonathan Kehayias (http://www.sqlskills.com/blogs/jonathan/) created the SQL Server 2008 Extended Events SSMS Addin which is really an excellent tool to ease XE session administration. This addin will install in SSMS 2008 or 2008R2 but not SSMS 2012. If you use a compatible version of SSMS then I wholly recommend downloading and using it to make your work with XE much easier.

If you have SSMS 2012 installed, and there is no reason not to as it will let you work with all versions of SQL Server, then you cannot install this addin. If you are working with SQL Server 2012 then SSMS 2012 has built in functionality to manage XE sessions – this functionality does not apply for 2008 or 2008 R2 instances though. This means you are somewhat restricted and have to use TSQL to manage XE sessions on older versions of SQL Server.

OK, those of you that skipped ahead for the code, you need to start from here: So, you are working with SSMS 2012 but have a SQL Server that is an earlier version that needs an XE session created or you think there is a session created but you aren’t sure, or you know it’s there but can’t remember if it is running and where the output is going. How do you find out? Well, none of the information is hidden as such but it is a bit of a wrangle to locate it and it isn’t a lot of code that is unlikely to remain in your memory.

I have created two pieces of code. The first examines the SYS.Server_Event_… management views in combination with the SYS.DM_XE_… management views to give the name of all sessions that exist on the server, regardless of whether they are running or not and two pieces of TSQL code. One piece will alter the state of the session: if the session is running then the code will stop the session if executed and vice versa. The other piece of code will drop the selected session. If the session is running then the code will stop it first. Do not execute the DROP code unless you are sure you have the Create code to hand. It will be dropped from the server without a second chance to change your mind.

/**************************************************************/ 
 /***   To locate and describe event sessions on a server    ***/ 
 /***                                                        ***/ 
 /***   Generates TSQL to start/stop/drop sessions           ***/ 
 /***                                                        ***/ 
 /***        Jonathan Allen - @fatherjack                    ***/ 
 /***                 June 2013                                ***/ 
 /***                                                        ***/ 
 /**************************************************************/ 

 SELECT  [EES].[name] AS [Session Name - all sessions] , 
         CASE WHEN [MXS].[name] IS NULL THEN ISNULL([MXS].[name], 'Stopped') 
              ELSE 'Running' 
         END AS SessionState , 
         CASE WHEN [MXS].[name] IS NULL 
              THEN ISNULL([MXS].[name], 
                          'ALTER EVENT SESSION [' + [EES].[name] 
                          + '] ON SERVER STATE = START;') 
              ELSE 'ALTER EVENT SESSION [' + [EES].[name] 
                   + '] ON SERVER STATE = STOP;' 
         END AS ALTER_SessionState , 
         CASE WHEN [MXS].[name] IS NULL 
              THEN ISNULL([MXS].[name], 
                          'DROP EVENT SESSION [' + [EES].[name] 
                          + '] ON SERVER; -- This WILL drop the session. It will no longer exist. Don't do it unless you are certain you can recreate it if you need it.') 
              ELSE 'ALTER EVENT SESSION [' + [EES].[name] 
                   + '] ON SERVER STATE = STOP; ' + CHAR(10) 
                   + '-- DROP EVENT SESSION [' + [EES].[name] 
                   + '] ON SERVER; -- This WILL stop and drop the session. It will no longer exist. Don't do it unless you are certain you can recreate it if you need it.' 
         END AS DROP_Session 
 FROM    [sys].[server_event_sessions] AS EES 
         LEFT JOIN [sys].[dm_xe_sessions] AS MXS ON [EES].[name] = [MXS].[name] 
 WHERE   [EES].[name] NOT IN ( 'system_health', 'AlwaysOn_health' ) 
 ORDER BY SessionState
GO

I have excluded the system_health and AlwaysOn sessions as I don’t want to accidentally execute the drop script for these sessions that are created as part of the SQL Server installation. It is possible to recreate the sessions but that is a whole lot of aggravation I’d rather avoid.

The second piece of code gathers details of running XE sessions only and provides information on the Events being collected, any predicates that are set on those events, the actions that are set to be collected, where the collected information is being logged and if that logging is to a file target, where that file is located.

/**********************************************/ 
 /***    Running Session summary                ***/ 
 /***                                        ***/ 
 /***    Details key values of XE sessions     ***/ 
 /***    that are in a running state            ***/ 
 /***                                        ***/ 
 /***        Jonathan Allen - @fatherjack    ***/ 
 /***        June 2013                        ***/ 
 /***                                        ***/ 
 /**********************************************/
SELECT  [EES].[name] AS [Session Name - running sessions] , 
         [EESE].[name] AS [Event Name] , 
         COALESCE([EESE].[predicate], 'unfiltered') AS [Event Predicate Filter(s)] , 
         [EESA].[Action] AS [Event Action(s)] , 
         [EEST].[Target] AS [Session Target(s)] , 
         ISNULL([EESF].[value], 'No file target in use') AS [File_Target_UNC] -- select * 
 FROM    [sys].[server_event_sessions] AS EES 
         INNER JOIN [sys].[dm_xe_sessions] AS MXS ON [EES].[name] = [MXS].[name] 
         INNER JOIN [sys].[server_event_session_events] AS [EESE] ON [EES].[event_session_id] = [EESE].[event_session_id] 
         LEFT JOIN [sys].[server_event_session_fields] AS EESF ON ( [EES].[event_session_id] = [EESF].[event_session_id] 
                                                               AND [EESF].[name] = 'filename' 
                                                               ) 
         CROSS APPLY ( SELECT    STUFF(( SELECT  ', ' + sest.name 
                                         FROM    [sys].[server_event_session_targets] 
                                                 AS SEST 
                                         WHERE   [EES].[event_session_id] = [SEST].[event_session_id] 
                                       FOR 
                                         XML PATH('') 
                                       ), 1, 2, '') AS [Target] 
                     ) AS EEST 
         CROSS APPLY ( SELECT    STUFF(( SELECT  ', ' + [sesa].NAME 
                                         FROM    [sys].[server_event_session_actions] 
                                                 AS sesa 
                                         WHERE   [sesa].[event_session_id] = [EES].[event_session_id] 
                                       FOR 
                                         XML PATH('') 
                                       ), 1, 2, '') AS [Action] 
                     ) AS EESA 
 WHERE   [EES].[name] NOT IN ( 'system_health', 'AlwaysOn_health' ) /*Optional to exclude 'out-of-the-box' traces*/

I hope that these scripts are useful to you and I would be obliged if you would keep my name in the script comments. I have no problem with you using it in production or personal circumstances, however it has no warranty or guarantee. Don’t use it unless you understand it and are happy with what it is going to do. I am not ever responsible for the consequences of executing this script on your servers.