Backup Deployed Sessions

There is a wealth of information within Extended Events. Throughout this series, I have been working on exposing that wealth of information. A good bit of this information happens to be the metadata for Extended Events, and another bit of this information is tied to the metadata for deployed Sessions. To dive into what I have done so far with this plenitude of information, you can review the series here.

What is the point in covering all of this information? I like to call it building blocks. Take a concept little by little and build upon it, while working toward a more comprehensive understanding as well as to hopefully be able to use it for a bigger product.

Having just completed several segments on the metadata for deployed sessions, it is time to bring all of that information together and see how it can be used.

Bringing it Together

Having a solid foundation and understanding of what the metadata is and where it is really helps in creating scripts that can be useful in the day to day management of deployed Extended Event Sessions. For me, the ability to recreate a deployed session, without the use of the GUI is pretty essential. Contributing to the need to be able to do this via script and not a GUI is that (without a third party product) there is no GUI in SQL Server 2008.

To demonstrate how to create a script to serve as a backup of a deployed session, I will first return to the “demosession” script that I have used throughout this series.

CREATE EVENT SESSION [demosession2] ON SERVER
ADD EVENT sqlserver.auto_stats ( 
	SET collect_database_name = ( 1 )			--fields
	ACTION (
			  package0.event_sequence		--actions
			, sqlos.cpu_id
			, sqlserver.database_id
			, sqlserver.database_name 
			)
	WHERE ( [database_name] = N'AdventureWorks2014' )	--events
	)
ADD TARGET package0.event_file 
	( SET filename = N'demosession'				--fields
		, max_file_size = ( 50 )			--fields
		, max_rollover_files = ( 6 )			--fields
			),
ADD TARGET package0.ring_buffer
		(SET max_events_limit=(666)			--fields
				,max_memory=(65536) --fields --max buffer size
				,occurrence_number=(3)		--fields
			)
WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS	--session
		, MAX_DISPATCH_LATENCY = 5 SECONDS		--session
		, MAX_EVENT_SIZE = 2 MB				--session
		, MEMORY_PARTITION_MODE = PER_NODE		--session
		, TRACK_CAUSALITY = ON				--session
		, STARTUP_STATE = ON				--session
	);
GO

CREATE EVENT SESSION [demosession2] ON SERVER

ADD EVENT sqlserver.auto_stats (

SET collect_database_name = ( 1 ) --fields

ACTION (

package0.event_sequence --actions

, sqlos.cpu_id

, sqlserver.database_id

, sqlserver.database_name

)

WHERE ( [database_name] = N'AdventureWorks2014' ) --events

)

ADD TARGET package0.event_file

( SET filename = N'demosession' --fields

, max_file_size = ( 50 ) --fields

, max_rollover_files = ( 6 ) --fields

ADD TARGET package0.ring_buffer

(SET max_events_limit=(666) --fields

,max_memory=(65536) --fields --max buffer size

,occurrence_number=(3) --fields

)

WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS --session

, MAX_DISPATCH_LATENCY = 5 SECONDS --session

, MAX_EVENT_SIZE = 2 MB --session

, MEMORY_PARTITION_MODE = PER_NODE --session

, TRACK_CAUSALITY = ON --session

, STARTUP_STATE = ON --session

);

And if I color-code this script as I have done previously:

In this image, I have separated out the major components by color. The segments that pertain to the session creation and the settings for that session are in grey. Then the target information is in red and so on. I added my notes (to help indicate where the components can be found in metadata) and colored them purple. The wrinkle here is in the SET operations since that is a component that crosses boundaries of the core concepts.

Taking this breakdown, and a serious desire to be able to recreate any session that has been deployed (without the GOOEY), and I have been able to create the following script that will do just that – backup deployed sessions.

SET NOCOUNT ON;

DECLARE @statement VARCHAR(MAX)
	,@statement1 VARCHAR(MAX)
	,@statement2 VARCHAR(MAX)
	,@sessionname VARCHAR(128) = 'system_health'
/* 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 gui scripter 

SELECT @statement = 'CREATE EVENT SESSION [' + ses.name +'] ON SERVER
	' 
	FROM sys.server_event_sessions ses
	WHERE ses.name = @sessionname;

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 = @sessionname
), 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 = @sessionname)
, 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 + '
			(SET ' + ca.setop + ' )
'
	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 = @sessionname
			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 = @sessionname;

PRINT @statement;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

SET NOCOUNT ON;

DECLARE @statement VARCHAR(MAX)

,@statement1 VARCHAR(MAX)

,@statement2 VARCHAR(MAX)

,@sessionname VARCHAR(128) = 'system_health'

/* 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 gui scripter

SELECT @statement = 'CREATE EVENT SESSION [' + ses.name +'] ON SERVER

FROM sys.server_event_sessions ses

WHERE ses.name = @sessionname;

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 = @sessionname

), 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 = @sessionname)

, 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 + '

(SET ' + ca.setop + ' )

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(