Log SQL Errors using Extended Events

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)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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