Script for creating an Extended Event on SQL Server to track completed queries

extended-event-queries-completed.sql

IF EXISTS (select * fROM sys.dm_xe_sessions where name = 'QueriesCompleted')
BEGIN
	DROP EVENT SESSION [QueriesCompleted] ON SERVER
END
GO

CREATE EVENT SESSION [QueriesCompleted] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.database_name,
		sqlserver.nt_username,
		sqlserver.server_principal_name,
		sqlserver.session_id,
		sqlserver.sql_text
	)
    WHERE (
		[package0].[equal_boolean]([sqlserver].[is_system],(0))
		--AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense') -- to exclude queries from a specific client app
		--AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'Some_Database_Name') -- to track a specific database
		AND [object_name]<>N'sp_reset_connection'
	)
),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.database_name,
		sqlserver.nt_username,
		sqlserver.server_principal_name,
		sqlserver.session_id,
		sqlserver.sql_text)
    WHERE (
		[package0].[equal_boolean]([sqlserver].[is_system],(0))
		--AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense') -- to exclude queries from a specific client app
		--AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'Some_Database_Name') -- to track a specific database
		AND [object_name]<>N'sp_reset_connection'
	)
)
ADD TARGET package0.event_file(
	SET filename=N'QueriesCompleted',
	max_file_size=(20),
	max_rollover_files=(5)
)
WITH (MAX_MEMORY=8192 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=ON)
GO

--start the event
ALTER EVENT SESSION [QueriesCompleted] ON SERVER STATE = START
GO