/*-------------------------------------------------------------------
[SCRIPT] User Errors xevent session
[DATABASE] master
[DESCRIPTION] create an xevent session for user SQL Errors
[MAJ PAR] DATAFLY - Arian Papillon
[DATE] 20190128
-------------------------------------------------------------------*/
USE master;
GO
DECLARE @XELPath NVARCHAR(MAX)
, @SQLCmd NVARCHAR(MAX);
SELECT @XELPath = [path] + 'SQLUserErrors.XEL'
FROM sys.dm_os_server_diagnostics_log_configurations;
SET @SQLCmd = 'CREATE EVENT SESSION SQLUserErrors
ON SERVER
ADD EVENT sqlserver.error_reported
(
-- ACTION( sqlserver.tsql_stack )
ACTION ( sqlserver.tsql_stack
, sqlserver.sql_text
, sqlserver.database_id
, sqlserver.username
, sqlserver.client_app_name
, sqlserver.client_hostname )
WHERE (( [error_number] <> 2528 -- DBCC execution completed...
AND [error_number] <> 3014 -- BACKUP LOG successfully processed ...
AND [error_number] <> 3262 -- The backup set on file is valid ...
AND [error_number] <> 4035 -- Processed 0 pages for database ...
AND [error_number] <> 5701 -- Changed database context to ,,,
AND [error_number] <> 5703 -- Changed language setting to ...
AND [error_number] <> 18265 -- Log was backed up. ...
AND [error_number] <> 8153 -- NULL Aggregate. ...
AND [error_number] <> 3211 -- Progression message. ...
AND [error_number] <> 14205 -- (unknown)
AND [error_number] <> 14213 -- Core Job Details:
AND [error_number] <> 14214 -- Job Steps:
AND [error_number] <> 14215 -- Job Schedules:
AND [error_number] <> 14216 -- Job Target Servers:
AND [error_number] <> 14549 -- (Description not requested.)
AND [error_number] <> 14558 -- (encrypted command)
AND [error_number] <> 14559 -- (append output file)
AND [error_number] <> 14560 -- (include results in history)
AND [error_number] <> 14561 -- (normal)
AND [error_number] <> 14562 -- (quit with success)
AND [error_number] <> 14563 -- (quit with failure)
AND [error_number] <> 14564 -- (goto next step)
AND [error_number] <> 14565 -- (goto step)
AND [error_number] <> 14566 -- (idle)
AND [error_number] <> 14567 -- (below normal)
AND [error_number] <> 14568 -- (above normal)
AND [error_number] <> 14569 -- (time critical)
AND [error_number] <> 14570 -- (Job outcome)
AND [error_number] <> 17550 -- DBCC Information
AND [error_number] <> 17551 -- DBCC Information
AND [error_number] <> 14635 -- Mail queued.
AND [error_number] <> 14638 -- Activation successful.
AND [error_number] <> 15477 -- Rename object
AND [error_number] <> 9911 -- alimentation Auto de texte intégral commencée
AND [error_number] <> 22866 -- Informations Change Tracking
AND [error_number] < 50000 -- Exclude User Errors
-- AND [database_id] = 8
-- AND ( [client_hostname] <> ''HOSTXYZ''
)))
-- Target File Mode
ADD TARGET package0.asynchronous_file_target
( SET filename = ''' + @XELPath
+ ''', max_file_size = 256, max_rollover_files = 4 )
WITH ( MAX_MEMORY = 4096KB
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 1 SECONDS
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = ON -- Very important to get the event in order later on
, STARTUP_STATE = ON -- Note that we start on
);';
EXEC sp_executesql @SQLCmd;
-- Start the session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'SQLUserErrors' )
ALTER EVENT SESSION SQLUserErrors
ON SERVER STATE = START;
ELSE
RAISERROR(
'ERROR : XEventSession SQLUserError NOT created, cannot start' , 19, 1) WITH LOG;
GO