Mass Backup All Sessions

,

Migrating Extended Event Sessions from one server to another should be a simple task. So simple, one would think there was no need to give it a second thought, right?

Well, I have previously written about this topic, you are welcome to read it here. The article discusses quite a bit about scripting out your XE Sessions. One thing lacking in that article is the ability to script out every session on a server.

If you are still not using Extended Events, I recommend checking out this library of articles that will cover just about all of the basics concerning Extended Events.

New and Improved

What about scripting out all of the sessions in SSMS? Surely there is an easy way to do that, right? Well, you might think that. Let me step through the problem that I have seen in SSMS (and unfortunately it is not consistent).

First, from Object Explorer Details (or F5), let’s try to script a single session.

When scripting a single session from the “Object Explorer Details”, I have several sub-menus that allow me to script the session to a “New Query Editor Window”. Now, let’s see what happens when trying to script multiple sessions.

With several sessions selected, I try yet again to script the sessions and I get an unwanted result. Notice that the “Script Session as” option is grayed out and unusable. However, if I try it again (several times or maybe just once, your mileage may vary and it seems to not be relevant to version of SSMS), I may see something like this.

Tada! Luck was with me and it finally worked that time. So, what should I do to be able to consistently script all of sessions? Well, that comes with an enhancement to the script I presented in the prior article here.

Lets just dive straight into the new script.

SET NOCOUNT ON;
DECLARE
	@statement	   VARCHAR(MAX)
  , @statement1	   VARCHAR(MAX)
  , @statement2	   VARCHAR(MAX)
  , @sessionname   VARCHAR(128) --	= 'AlwaysOn_health'						 -- 'system_health'
  , @csessionname  VARCHAR(128)
  , @XeFilePath	   VARCHAR(256)
  , @IsDefaultPath TINYINT
											 /* order of events scripted is different 
default order of creation is different than if scripted from the gui
the gui scripts by alpha order of package then event
while original creation has order by event_id only
*/
  , @eventorder	   VARCHAR(64) = 'original'; --'original' for order based on original script, --'ssms' for order based on ssms guy scripter 
  /* todo:
  add option to exclude default/system sessions
  add ability to do this for azure db
  */
DECLARE xessions CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
	SELECT
				 ses.name
		FROM	 sys.server_event_sessions ses
		WHERE	 ses.name = ISNULL(@sessionname, ses.name)
		ORDER BY ses.name;
OPEN xessions;
FETCH NEXT FROM xessions
INTO
	@csessionname;
DECLARE @ErrorLog VARCHAR(256) = CONVERT(VARCHAR(256), SERVERPROPERTY('ErrorLogFileName'));
DECLARE @ErrorPath VARCHAR(256) = LEFT(@ErrorLog, LEN(@ErrorLog) - CHARINDEX('', REVERSE(@ErrorLog)) + 1);
WHILE @@FETCH_STATUS = 0
	BEGIN
		IF EXISTS
			(
				SELECT
								   1
					FROM		   sys.server_event_sessions	   ses
						INNER JOIN sys.server_event_session_fields sesf
							ON ses.event_session_id = sesf.event_session_id
					WHERE		   sesf.name = 'filename'
								   AND ses.name = @csessionname
			)
			BEGIN
				IF
					(
						SELECT
										   CHARINDEX('', CONVERT(VARCHAR(256), sesf.value))
							FROM		   sys.server_event_sessions	   ses
								INNER JOIN sys.server_event_session_fields sesf
									ON ses.event_session_id = sesf.event_session_id
							WHERE		   sesf.name = 'filename'
										   AND ses.name = @csessionname
					) > 0
					BEGIN
						SELECT
										   @XeFilePath
							= LEFT(CONVERT(VARCHAR(256), sesf.value), LEN(CONVERT(VARCHAR(256), sesf.value))
																	  - CHARINDEX(
																					 ''
																				   , REVERSE(CONVERT(
																										VARCHAR(256)
																									  , sesf.value
																									)
																							)
																				 ) + 1)
							FROM		   sys.server_event_sessions	   ses
								INNER JOIN sys.server_event_session_fields sesf
									ON ses.event_session_id = sesf.event_session_id
							WHERE		   sesf.name = 'filename'
										   AND ses.name = @csessionname;
					END;
				ELSE
					BEGIN
						IF
							(
								SELECT				TOP 1
													LEFT(t2.file_name, LEN(t2.file_name) - CHARINDEX('', REVERSE(t2.file_name)) + 1) AS XEFilePath
									FROM
													(
														SELECT
																		   REPLACE(CONVERT(NVARCHAR(128), sesf.value), '.xel', '*.xel') AS targetvalue
																		 , ses.name														AS SessionName
															FROM		   sys.server_event_sessions	   ses
																INNER JOIN sys.server_event_session_fields sesf
																	ON ses.event_session_id = sesf.event_session_id
															WHERE		   sesf.name = 'filename'
																		   AND ses.name = @csessionname
													)																	cte1
										CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue, NULL, NULL, NULL) t2
							) IS NOT NULL
							BEGIN
								SELECT
									@XeFilePath
									=
									(
										SELECT				TOP 1
															LEFT(t2.file_name, LEN(t2.file_name)
																			   - CHARINDEX('', REVERSE(t2.file_name)) + 1) AS XEFilePath
											FROM
															(
																SELECT
																				   REPLACE(CONVERT(NVARCHAR(128), sesf.value), '.xel', '*.xel') AS targetvalue
																				 , ses.name														AS SessionName
																	FROM		   sys.server_event_sessions	   ses
																		INNER JOIN sys.server_event_session_fields sesf
																			ON ses.event_session_id = sesf.event_session_id
																	WHERE		   sesf.name = 'filename'
																				   AND ses.name = @csessionname
															)								 cte1
												CROSS APPLY sys.fn_xe_file_target_read_file(
																							   cte1.targetvalue
																							 , NULL
																							 , NULL
																							 , NULL
																						   ) t2
									);
							END;
						ELSE
							BEGIN
								SELECT
									@XeFilePath = @ErrorPath;
								SELECT
									@XeFilePath
								  , @ErrorPath;
							END;
					END;
			END;
		IF @ErrorPath = @XeFilePath
			BEGIN
				SET @IsDefaultPath = 1;
			END;
		ELSE
			BEGIN
				SET @IsDefaultPath = 0;
			END;
		SELECT
				  @statement
			= 'USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
				FROM sys.server_event_sessions
				WHERE name = ' + ses.name + ' )
	DROP EVENT SESSION ' + ses.name + ' 
    ON SERVER;
GO
'
			FROM  sys.server_event_sessions ses
			WHERE ses.name = @csessionname;
		IF @IsDefaultPath = 1
			BEGIN
				SET @statement = @statement + '';
			END;
		ELSE
			BEGIN
				SET @statement = @statement + '
EXECUTE xp_create_subdir '''	 + @XeFilePath + ''';
GO
'				;
			END;
		SELECT
				  @statement = @statement + '
CREATE EVENT SESSION ['	 + ses.name + '] ON SERVER
	'
			FROM  sys.server_event_sessions ses
			WHERE ses.name = @csessionname;
		WITH presel AS
			(SELECT
									 ses.name											  AS SessionName
								   , sese.event_id
								   , sese.package
								   , 'ADD EVENT ' + sese.package + '.' + sese.name + ' (' AS EventName
								   , CASE
										 WHEN ISNULL(sese.predicate, '') = ''
											  THEN ')'
										 ELSE 'WHERE ' + sese.predicate + ' )'
									 END												  AS SQLPredicate
								   , CASE
										 WHEN ISNULL(sesf.name, '') = ''
											  THEN ''
										 ELSE 'SET ' + sesf.name + ' = ( ' + CAST(sesf.value AS VARCHAR(10)) + ' )'
									 END												  AS SETOperation
				 FROM				 sys.server_event_sessions		 ses
					 INNER JOIN		 sys.server_event_session_events sese
						 ON ses.event_session_id = sese.event_session_id
					 LEFT OUTER JOIN sys.server_event_session_fields sesf
						 ON sese.event_session_id = sesf.event_session_id
							AND sese.event_id = sesf.object_id
				 WHERE				 ses.name = @csessionname)
		   , actpresel AS
			(SELECT
								ses.name					   AS SessionName
							  , sesa.event_session_id
							  , sesa.event_id
							  , sesa.package + '.' + sesa.name AS ActPack
				 FROM			sys.server_event_session_events	 sese
					 INNER JOIN sys.server_event_session_actions sesa
						 ON sese.event_session_id = sesa.event_session_id
							AND sese.event_id = sesa.event_id
					 INNER JOIN sys.server_event_sessions		 ses
						 ON sesa.event_session_id = ses.event_session_id
				 WHERE			ses.name = @csessionname)
		   , pstuff AS
			(SELECT
						  p.SessionName
						, p.event_id
						, 'ACTION ( 
				' +		STUFF((
								  SELECT
											', ' + pin.ActPack + '
				'
									  FROM	actpresel pin
									  WHERE pin.SessionName = p.SessionName
											AND pin.event_id = p.event_id
								  FOR XML PATH(''), TYPE
							  ).value('.', 'varchar(max)')
							, 1
							, 2
							, ''
							 ) + '
			)'			AS ActionPack
				 FROM	  actpresel p
				 GROUP BY p.SessionName
						, p.event_id)
		SELECT
			@statement1 = @statement + STUFF((
												 SELECT
																		 ', ' + CAST(p.EventName + '
		'														 + p.SETOperation + '
		'														 + CASE
																	   WHEN ISNULL(ps.ActionPack, '') = ''
																			THEN ''
																	   ELSE ps.ActionPack
																   END + '
		'														 + p.SQLPredicate + '
'													 AS VARCHAR(MAX)) AS EventStatement
													 FROM				 presel p
														 LEFT OUTER JOIN pstuff ps
															 ON p.SessionName = ps.SessionName
																AND p.event_id = ps.event_id
													 ORDER BY			 p.event_id
												 --ORDER BY p.package, p.EventName 
												 FOR XML PATH(''), TYPE
											 ).value('.', 'varchar(max)')
										   , 1
										   , 2
										   , ''
											)
		  , @statement2 = @statement + STUFF((
												 SELECT
																		 ', ' + CAST(p.EventName + '
		'														 + p.SETOperation + '
		'														 + CASE
																	   WHEN ISNULL(ps.ActionPack, '') = ''
																			THEN ''
																	   ELSE ps.ActionPack
																   END + '
		'														 + p.SQLPredicate + '
'													 AS VARCHAR(MAX)) AS EventStatement
													 FROM				 presel p
														 LEFT OUTER JOIN pstuff ps
															 ON p.SessionName = ps.SessionName
																AND p.event_id = ps.event_id
													 ORDER BY			 p.package
																	   , p.EventName
												 FOR XML PATH(''), TYPE
											 ).value('.', 'varchar(max)')
										   , 1
										   , 2
										   , ''
											);
		IF @eventorder = 'original'
			BEGIN
				SET @statement = @statement1;
			END;
		IF @eventorder = 'ssms'
			BEGIN
				SET @statement = @statement2;
			END;
		SELECT
			@statement
			= @statement
			  + STUFF(
			(
				SELECT
								   ', ' + '
ADD TARGET '										+ sest.package + '.' + sest.name
								   + CASE
										 WHEN ISNULL(ca.setop, '') = ''
											  THEN ' 
'
										 ELSE '
			(SET ' +								ca.setop + ' )
'
									 END
					FROM		   sys.server_event_sessions		ses
						INNER JOIN sys.server_event_session_targets sest
							ON ses.event_session_id = sest.event_session_id
						CROSS APPLY
								   (
									   SELECT
										   STUFF(
			(
				SELECT
								   ', ' + CAST(sesf.name AS VARCHAR(128)) + ' = '
								   + CASE
										 WHEN ISNUMERIC(CAST(sesf.value AS VARCHAR(128))) = 1
											  THEN '( ' + CAST(sesf.value AS VARCHAR(128)) + ' )
							'
										 ELSE 'N''' + CAST(sesf.value AS VARCHAR(128)) + '''
						'
									 END AS setop
					FROM		   sys.server_event_session_fields	sesf
						INNER JOIN sys.server_event_sessions		sesi
							ON sesi.event_session_id = sesf.event_session_id
						INNER JOIN sys.server_event_session_targets sesti
							ON sesf.object_id = sesti.target_id
							   AND sesi.event_session_id = sesti.event_session_id
					WHERE		   sest.target_id = sesti.target_id
								   AND sest.event_session_id = sesti.event_session_id
				FOR XML PATH(''), TYPE
			).value('.', 'varchar(max)')
		  , 1
		  , 2
		  , ''
												)
								   )								AS ca(setop)
					WHERE		   ses.name = @csessionname
				FOR XML PATH(''), TYPE
			).value('.', 'varchar(max)')
		  , 1
		  , 2
		  , ''
					 );
		SELECT
				  @statement
			= @statement
			  + CAST('WITH ( MAX_MEMORY = ' + CAST(ses.max_memory AS VARCHAR(20)) + ' KB
		,EVENT_RETENTION_MODE = '		   + ses.event_retention_mode_desc + '
		, MAX_DISPATCH_LATENCY = '		   + CAST(ses.max_dispatch_latency / 1000 AS VARCHAR(20))
					 + ' SECONDS
		, MAX_EVENT_SIZE = '			   + CAST(ses.max_event_size AS VARCHAR(20))
					 + ' KB
		, MEMORY_PARTITION_MODE = '		   + ses.memory_partition_mode_desc + '
		, TRACK_CAUSALITY = '			   + CASE ses.track_causality
												 WHEN 1
													  THEN 'ON'
												 ELSE 'OFF'
											 END + '
		, STARTUP_STATE = '				   + CASE ses.startup_state
												 WHEN 1
													  THEN 'ON'
												 ELSE 'OFF'
											 END + '
	);'		AS VARCHAR(1024))
			FROM  sys.server_event_sessions ses
			WHERE ses.name = @csessionname;
		PRINT @statement;
		FETCH NEXT FROM xessions
		INTO
			@csessionname;
	END;
CLOSE xessions;
DEALLOCATE xessions;

This is a rather lengthy script, so I won’t explain the entire thing. That said, this script will produce the exact XE Session as it was written when you deployed it to the server. In addition, the script will ensure the destination directory for the event_file target is created as a part of the script.

I can definitely hear the gears of thought churning as you ponder about this whole scenario. Surely, you have all of your XE Sessions stored in source control so there is no need whatsoever for this little script. Then again, that would be in an ideal environment. Sadly, source control is seldom considered for XE Sessions. Thus, it is always good to have a backup plan.

Why

Sadly, I had the very need of migrating a ton of sessions from one server to another recently and the methods in SSMS just wouldn’t work. There was no source control in the environment. Building out this little script saved me tons of time in migrating all of the sessions for this server and also provided me with a good script to place in source control.

Conclusion

In the article today, I have provided an excellent tool for backing up all of your XE sessions on the server. This script will help create the necessary scripts for all of your XE Sessions (or even just a single session if you like) in order to migrate the sessions to a new server or place them in source control.

To read more about Extended Events, I recommend this series of articles.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate