How to use extended events to catch all error in SQL Server Himanshu Patel SQL Server Consultant developer & Administrator -

SQL Server The Extended Events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem. Extended Events is configurable, and it scales very well.

Benefits Extended Events

Extended Events is a lightweight performance monitoring system that uses minimal performance resources. Extended Events provides two graphical user interfaces to create, modify, display, and analyze your session data. These interfaces are named:

--Lists all the event session definitions that exist in SQL Server.
select * from sys.server_event_sessions 
--Returns a row for each customizable column that was explicitly set on events and targets.
select * from sys.server_event_session_fields 
--Returns a row for each action on each event of an event session.
select * from sys.server_event_session_actions	
--Returns a row for each event target for an event session.
select * from sys.server_event_session_targets
--Returns a row for each event in an event session.
select * from sys.server_event_session_events

--->>>catch all error
--Create an extended event session
CREATE EVENT SESSION [CatchError] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname
	,sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([severity]>(10)))
ADD TARGET package0.event_file(SET filename=N'CatchError.xel'
,max_file_size=(5),max_rollover_files=(5),metadatafile=N'CatchError.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB
,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the session
ALTER EVENT SESSION CatchError ON SERVER STATE = START
GO

;with events_cte as(
select DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],
xevents.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity],
xevents.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number],
xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(512)') AS client_hostname,
xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(512)') AS client_app_name,
xevents.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message],
xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
xevents.event_data
from sys.fn_xe_file_target_read_file('CatchError*.xel',
'CatchError*.xem',null, null)
cross apply (select CAST(event_data as XML) as event_data) as xevents
)
SELECT * from events_cte order by err_timestamp;