Sql Server – Extended Events – Creating in Later Edition and Applying Against 2008/2008R2

Background

Ungratefulness is a sad part of LIFE that it is hard to see and acknowledge.

 

Scenario

One of our databases there is a reporting table that contains aggregated data for transactions that occur each month.

Things are working, but since the person that designed the Application “walked“, we do not have an insight as when it is ran, who runs it, and where it is being ran.

The end of month was yesterday and I need to quickly see what I can find out.

 

Circumstances

Unfortunately, the SQL Instance is on v2008-R2 and that version does not have a GUI for Extended Event Management.

 

Client – SSMS

Object Explorer

Object Explorer Against v2014 Instance

 

 

Object Explorer Against v2008/R2 Instance

Comparison

When the targeted instance is on v2012 or higher SSMS, Extended Event Management functionality is visible under the SQL Instance \ Management node.

But when the SQL Instance is v2008 or v2008/R2, though Extended Event ( EE ) sub-component is available under the hood, it is not exposed via the SSMS GUI.

 

Work Around

Create Baseline

We created a simple package against our v2014 SQL Instance.

Once created, we asked SSMS to please script it for us.

To do so, please access “Management” \ “Extended Events” \ Sessions \ [session] \ “Script Session as” \ “Create To”

 

 

Generated Script


CREATE EVENT SESSION [thirdPartyAppRequest] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[client_app_name]<>N'Report Server')),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[client_app_name]<>N'Report Server')),
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[client_app_name]<>N'Report Server')) 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO



Apply Script Against v2008-R2 SQL Instance

Errors

 

Error # Error Text Response
 Msg 25623  The event action name, “sqlserver.database_name”, is invalid, or the object could not be found
 sql_server.database_name is not exposed in v2008/R2.
Please use sql_server.database_id
 Msg 25623  The event action name, “sqlserver.server_principal_name”, is invalid, or the object could not be found   sql_server.server_principal_name is not exposed in v2008/R2.
Please use sql_server.username
 Msg 25629  For event, “sqlserver.sp_statement_completed”, the customizable attribute, “collect_object_name”, does not exist.  Comment this out “SET collect_object_name=(1)

 

 

Cleaned Up Script For v2008-R2 SQL Instance

Script


declare @extendedEvent sysname

set @extendedEvent = 'thirdPartyAppRequestScripted'

if exists
(

	select *

	from    sys.server_event_sessions tblSSES

	where   tblSSES.[name] = @extendedEvent

)
begin

	print 'Drop XE ' + @extendedEvent

	DROP EVENT SESSION [thirdPartyAppRequestScripted] 
		ON SERVER 

end



CREATE EVENT SESSION [thirdPartyAppRequestScripted] ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
	(
		  package0.collect_system_time
		, sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.client_pid
		, sqlserver.database_id
	--	, sqlserver.database_name
	--	, sqlserver.server_principal_name
		, sqlserver.session_id
		, sqlserver.sql_text
		, sqlserver.username
	)
    WHERE 
	(
		    [package0].[greater_than_uint64]([sqlserver].[database_id],(4)) 
		AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
	)
)

, ADD EVENT sqlserver.rpc_completed
(
    ACTION
	(
		  package0.collect_system_time
		, sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.client_pid
		, sqlserver.database_id
		--, sqlserver.database_name
		--, sqlserver.server_principal_name
		, sqlserver.session_id
		, sqlserver.sql_text
		, sqlserver.username
	)
    WHERE 
	(
		    [package0].[greater_than_uint64]([sqlserver].[database_id],(4)) 
		AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
		AND [sqlserver].[client_app_name]<>N'Report Server'
	)

)	

, ADD EVENT sqlserver.sp_statement_completed
   (
	
	--Msg 25629, Level 16, State 1, Line 2
	-- For event, "sqlserver.sp_statement_completed", the customizable attribute, "collect_object_name", does not exist
	--SET collect_object_name=(1)
    
	ACTION
	(
		  package0.collect_system_time
		, sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.client_pid
		, sqlserver.database_id
		--, sqlserver.database_name
		--, sqlserver.server_principal_name
		, sqlserver.session_id
		, sqlserver.sql_text
		, sqlserver.username
	)
    
	WHERE 
	(
		    [package0].[greater_than_uint64]([sqlserver].[database_id],(4)) 
		AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
		AND [sqlserver].[client_app_name]<>N'Report Server'
	)
	
)	


, ADD EVENT sqlserver.sql_statement_completed
	(
		
		--SET collect_statement=(1)
		
		ACTION
		(
			  package0.collect_system_time
			, sqlserver.client_app_name
			, sqlserver.client_hostname
			, sqlserver.client_pid
			, sqlserver.database_id
			--, sqlserver.database_name
			--, sqlserver.server_principal_name
			, sqlserver.session_id
			, sqlserver.sql_text
			, sqlserver.username
		)
		WHERE 
		(
				 [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
			 AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
			 AND [sqlserver].[client_app_name]<>N'Report Server')
		) 


ADD TARGET package0.ring_buffer

WITH 
(
	  MAX_MEMORY=4096 KB
	, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
	, MAX_DISPATCH_LATENCY=30 SECONDS
	, MAX_EVENT_SIZE=0 KB
	, MEMORY_PARTITION_MODE=NONE
	, TRACK_CAUSALITY=ON
	, STARTUP_STATE=OFF
)

if exists
(

	select *

	from    sys.server_event_sessions tblSSES

	where   tblSSES.[name] = @extendedEvent

)
begin

	print 'Created XE ' + @extendedEvent

end


For Persistency

Script

    ADD TARGET package0.event_file 
     ( 
          SET 
              filename='E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance'
            , max_file_size=(100) 
            , max_rollover_files=(100) 
     )
 

 

Error Message

Error # Error Text Response
Msg 25623  The target name, “package0.event_file”, is invalid, or the object could not be found
 It seems package0.event_file is not supported.
Msg 25641  For target, “package0.asynchronous_file_target”, the parameter “filename” passed is invalid.
The operating system returned error 2 while creating the file ‘T:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance.xel’.
Verify that the path exists and that the SQL Server startup account has access to the location.
 Change targeted folder name to a directory that exists and that the Service Account has access to.
 Msg 25641  For target, “package0.asynchronous_file_target”, the parameter “metadatafile” passed is invalid.
The operating system returned error 2 while creating the file ‘J:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance.xem’.
Verify that the path exists and that the SQL Server startup account has access to the location.
 Change metadatafile’s directory to one that exists and SQL Server Account has access to.

 

 

Script ( Revised )



ADD TARGET package0.asynchronous_file_target

(
   SET 
             filename='E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance.xel'
	   , metadatafile='E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance.xem'
	   , max_file_size=(100)
 	   , max_rollover_files=(100)

)


 

Full SQL

Script


declare @extendedEvent sysname

set @extendedEvent = 'thirdPartyAppRequestScripted'

if exists
(

	select *

	from    sys.server_event_sessions tblSSES

	where   tblSSES.[name] = @extendedEvent

)
begin

	print 'Drop XE ' + @extendedEvent

	DROP EVENT SESSION [thirdPartyAppRequestScripted] 
		ON SERVER 

end



CREATE EVENT SESSION [thirdPartyAppRequestScripted] ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
	(
		  package0.collect_system_time
		, sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.client_pid
		, sqlserver.database_id
	--	, sqlserver.database_name
	--	, sqlserver.server_principal_name
		, sqlserver.session_id
		, sqlserver.sql_text
		, sqlserver.username
	)
    WHERE 
	(
		    [package0].[greater_than_uint64]([sqlserver].[database_id],(4)) 
		AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
	)
)

, ADD EVENT sqlserver.rpc_completed
(
    ACTION
	(
		  package0.collect_system_time
		, sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.client_pid
		, sqlserver.database_id
		--, sqlserver.database_name
		--, sqlserver.server_principal_name
		, sqlserver.session_id
		, sqlserver.sql_text
		, sqlserver.username
	)
    WHERE 
	(
		    [package0].[greater_than_uint64]([sqlserver].[database_id],(4)) 
		AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
		AND [sqlserver].[client_app_name]<>N'Report Server'
	)

)	

, ADD EVENT sqlserver.sp_statement_completed
   (
	
	--Msg 25629, Level 16, State 1, Line 2
	-- For event, "sqlserver.sp_statement_completed", the customizable attribute, "collect_object_name", does not exist
	--SET collect_object_name=(1)
    
	ACTION
	(
		  package0.collect_system_time
		, sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.client_pid
		, sqlserver.database_id
		--, sqlserver.database_name
		--, sqlserver.server_principal_name
		, sqlserver.session_id
		, sqlserver.sql_text
		, sqlserver.username
	)
    
	WHERE 
	(
		    [package0].[greater_than_uint64]([sqlserver].[database_id],(4)) 
		AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
		AND [sqlserver].[client_app_name]<>N'Report Server'
	)
	
)	


, ADD EVENT sqlserver.sql_statement_completed
	(
		
		--SET collect_statement=(1)
		
		ACTION
		(
			  package0.collect_system_time
			, sqlserver.client_app_name
			, sqlserver.client_hostname
			, sqlserver.client_pid
			, sqlserver.database_id
			--, sqlserver.database_name
			--, sqlserver.server_principal_name
			, sqlserver.session_id
			, sqlserver.sql_text
			, sqlserver.username
		)
		WHERE 
		(
				 [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
			 AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) 
			 AND [sqlserver].[client_app_name]<>N'Report Server')
		) 


ADD TARGET package0.ring_buffer

WITH 
(
	  MAX_MEMORY=4096 KB
	, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
	, MAX_DISPATCH_LATENCY=30 SECONDS
	, MAX_EVENT_SIZE=0 KB
	, MEMORY_PARTITION_MODE=NONE
	, TRACK_CAUSALITY=ON
	, STARTUP_STATE=OFF
)

if exists
(

	select *

	from    sys.server_event_sessions tblSSES

	where   tblSSES.[name] = @extendedEvent

)
begin

	print 'Created XE ' + @extendedEvent

end


if exists
(

	select *

	from    sys.server_event_sessions tblSSES

	where   tblSSES.[name] = @extendedEvent

)
begin

	print 'Start XE ' + @extendedEvent

	ALTER EVENT SESSION [thirdPartyAppRequestScripted] 
		ON SERVER 
			STATE=START;

end



Dedicated

Dedicated to ATL, I85 has enough going on already.

Have hard weeks and months ahead.

And, to Microsoft for making our lives a lot easier.

 

One thought on “Sql Server – Extended Events – Creating in Later Edition and Applying Against 2008/2008R2

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s