XEVENT: Logged when SQL Server has a user-level error

System Information Level: http://blog.sqlauthority.com/2007/04/25/sql-server-error-messages-sysmessages-error-severity-level/    Test: BEGIN TRY--Generate a divide-by-zero error. SELECT 1/0; END trybegin CATCH SELECT error_severity () as errorseverity; END CATCH, when SQL Server has a user-level error, record create event session xevents_session on Serveradd event sqlserver.error_reported (Action ( sqlserver.session_id,--SPID which raises the Errorsqlserver.plan_handle,--plan handle which can be used to retrieve th E graphical Plansqlserver.tsql_stack,--t-SQL stack for extended debugging Purposespackage0.callstack,--callstack for E  xtended debugging Purposessqlserver.sql_text,--T-SQL query which encountered the Errorsqlserver.username,--Name of the  User that reported the Errorsqlserver.client_app_name,--client application Namesqlserver.client_hostname,--Host which Initiated the Querysqlserver.database_name--database against which the query was being executed) WHERE severity >= 1 1 and Severity <=16) Add target packAge0.ring_bufferwith (max_dispatch_latency=1seconds) go--start the Tracingalter event session xevents_session on server state = Start--alter Event session xevents_session on Server state = Stop--drop Event session xevents_session on server-- See Select N.value (' (event/@name) [1] ', ' varchar (+) ') as Event_Name, N.value (' (event/@package) [1] ', ' varchar ') as P Ackage_name, DATEADD (hh, DATEDIFF (hh, getUTCDate (), Current_timestamp), N.value (' (event/@timestam p) [1] ', ' datetime2 ')) as [Timestamp],n.value (' (event/action[@name = "database_name"]/value) [1] ', ' nvarchar (+) ') as [ database_name], N.value (' (event/action[@name = "Sql_text"]/value) [1] ', ' nvarchar (max) ') as [Sql_text],n.value (' ( event/data[@name = "message"]/value) [1] ', ' nvarchar (max) ') as [Message],n.value (' (event/action[@name = "username"]/ Value) [1] ', ' nvarchar (max) ') as [Username],n.value (' (event/action[@name = "Client_hostname"]/value) [1] ', ' nvarchar ( Max) as [Client_hostname],n.value (' (event/action[@name = "cliEnt_app_name "]/value" [1] ', ' nvarchar (max) ') as [Client_app_name],n.value (' (event/action[@name = "session_id"]/value [1] ', ' nvarchar (max) ') as [Session_id],n.value (' (event/action[@name = "plan_handle"]/value) [1] ', ' nvarchar (max) ') as [Plan_handle],n.value (' (event/action[@name = "Tsql_stack"]/value/frames/frame/@handle) [1] ', ' varchar (max) ') as [ Tsql_stack],n.value (' (event/action[@name = "Tsql_stack"]/value/frames/frame/@offsetStart) [1] ', ' int ') as [statement _start_offset],n.value (' (event/action[@name = "Tsql_stack"]/value/frames/frame/@offsetEnd) [1] ', ' int ') as [ Statement_end_offset],n.value (' (event/action[@name = "CallStack"]/value) [1] ', ' nvarchar (max) ') as [callstack], N.value (' (event/data[@name = "object_id"]/value) [1] ', ' int ') as [object_id], N.value (' (event/data[@name = "object_type "]/value" [1] ', ' nvarchar (+) ') as [object_type], N.value (' (event/data[@name = "duration"]/value) [1] ', ' int ') as [ Duration], N.value (' (event/data[@name = "CPU"]/value) [1] ', ' int ') as [CPU], N.value (' (event/data[@nAme= "reads"]/value) [1] ', ' int ') as [reads], N.value (' (event/data[@name = "writes"]/value) [1] ', ' int ') as [writes] into #1 120FROM (select Td.query ('. ') as n from (select CAST (Target_data as XML) as Target_data from SYS.D M_xe_sessions as S JOIN sys.dm_xe_session_targets as T on t.event_session_address = s.address WHE RE s.name = ' xevents_session '--and t.target_name = ' Ring_buffer ') as sub cross APPLY target_data.nodes (' R Ingbuffertarget/event ') as Q (TD)) as tab--view a select Timestamp,database_name,[message],sql_text,username,client_ with SQL Hostname,client_app_namefrom #1120 awhere A.sql_text! = "--View the app execution's select Timestamp,database_name,[message], SUBSTRING (qt.text,a.statement_start_offset/2+1, (case is A.statement_end_offset = -1then datalength (qt.text) Else A.statement_end_offset End-a.statement_start_offset)/2 + 1) sql_text,qt.text Alltext,username,client_hostname, Client_app_namefrom #1120 ACROSS APPLY sys.dm_exec_sql_text (CONVERT (VARBINARY (max), a.tsql_stack,1)) Qtwhere A.sql_text is NULL and tsql_stack! = ' and Client_app_name = '. Net sqlclien T Data Provider ' ORDER by TIMESTAMP DESC

XEVENT: Logged when SQL Server has a user-level error