Blog Post

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 
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
(
SELECTTOP 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.nameAS 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
=
(
SELECTTOP 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.nameAS 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.EventSetOps, '') = ''
 THEN ''
ELSE 'SET ' + sesf.EventSetOps + ''
END AS SETOperation
 FROMsys.server_event_sessionsses
 INNER JOIN sys.server_event_session_events sese
 ON ses.event_session_id = sese.event_session_id
 LEFT OUTER JOIN
(
SELECT DISTINCT
 ST2.event_session_id
   , ST2.object_id
   , SUBSTRING(
 (
 SELECT
', ' + ST1.name + '=' + '(' + CONVERT(VARCHAR(60), ST1.value) + ')' AS [text()]
 FROMsys.server_event_session_fields ST1
 INNER JOIN sys.server_event_session_events sese
 ON sese.event_session_id = ST1.event_session_id
AND sese.event_id = ST1.object_id
 WHEREST1.event_session_id = ST2.event_session_id
AND ST1.object_id = ST2.object_id
 ORDER BYST1.object_id
 FOR XML PATH('')
 )
   , 3
   , 8000
  ) [EventSetOps]
FROM sys.server_event_session_fields ST2
)sesf
 ON sese.event_session_id = sesf.event_session_id
AND sese.event_id = sesf.object_id
 WHEREses.name = @csessionname)
   , actpresel AS
(SELECT
ses.name   AS SessionName
  , sesa.event_session_id
  , sesa.event_id
  , sesa.package + '.' + sesa.name AS ActPack
 FROMsys.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
 WHEREses.name = @csessionname)
   , pstuff AS
(SELECT
  p.SessionName
, p.event_id
, 'ACTION ( 
' +STUFF((
  SELECT
', ' + pin.ActPack + '
'
  FROMactpresel 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_sessionsses
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_fieldssesf
INNER JOIN sys.server_event_sessionssesi
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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating