First create the extended event session to monitor for any errors with severity grater than equal to 11
CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported (ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.sql_text, sqlserver.username) WHERE ([severity] >= (11))) ADD TARGET package0.event_file ( SET filename = N'ErrorCapture' ,max_file_size = (100) ,max_rollover_files = (5) ) 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 = OFF ,STARTUP_STATE = ON ) GO
How to view them in TSQL
DECLARE @error_log VARCHAR(max) = convert(VARCHAR(max), SERVERPROPERTY('ErrorLogFileName')) DECLARE @error_log_dir VARCHAR(max) = reverse(substring(reverse(@error_log), charindex('\', reverse(@error_log)), len(@error_log))) SELECT * FROM sys.fn_xe_file_target_read_file(@error_log_dir + 'errorcap*.xel', NULL, NULL, NULL)
