Extended Events - SQL Errors

This script creates an Extended Events Session (XEvent) to capture SQL errors. Very useful to detect the object and the statement that is raising the error. 


The first part creates the session and the second one is to retrieve the errors that are been raising.




IMPORTANT: This is a template so make sure you enter the proper values (Path and session name)

USE master

GO

-- Create XEvents Session

exec master.dbo.xp_cmdshell 'Dir "< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >*.*"'

CREATE EVENT SESSION < XEvents_Session_Name,varchar(1000),sql_text_and_errors >

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] <>  2528 -- DBCC execution completed...

AND [error] <>  3014 -- BACKUP LOG successfully processed ...

AND [error] <>  4035 -- Processed 0 pages for database ...

AND [error] <>  5701 -- Changed database context to ,,,

AND [error] <>  5703 -- Changed language setting to ...

AND [error] <>  18265 -- Log was backed up. ...

AND [error] <>  14205 -- (unknown)

AND [error] <> 14213 -- Core Job Details:

AND [error] <> 14214 -- Job Steps:

AND [error] <> 14215 -- Job Schedules:

AND [error] <> 14216 -- Job Target Servers:

AND [error] <>  14549 -- (Description not requested.)

AND [error] <>  14558 -- (encrypted command)

AND [error] <>  14559 -- (append output file)

AND [error] <>  14560 -- (include results in history)

AND [error] <>  14561 -- (normal)

AND [error] <>  14562 -- (quit with success)

AND [error] <>  14563 -- (quit with failure)

AND [error] <>  14564 -- (goto next step)

AND [error] <>  14565 -- (goto step)

AND [error] <>  14566 -- (idle)

AND [error] <>  14567 -- (below normal)

AND [error] <>  14568 -- (above normal)

AND [error] <>  14569 -- (time critical)

AND [error] <>  14570 -- (Job outcome)

AND [error] <>  14635 -- Mail queued.

AND [error] <> 14638 -- Activation successful.

AND [error] <= 50000 -- Exclude User Errors

)))

----------------------------------------------------------------------------------

-- Target File Mode

-- ****** Make sure you have enough disk space - Also you must monitor the disk while the session is running ******

ADD TARGET package0.asynchronous_file_target

(SET filename='< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >'

,max_file_size=4000

)

----------------------------------------------------------------------------------

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

);

----------------------------------------------------------------------------------------------

-- STEP: Start the session & see which sessions are currently running

ALTER EVENT SESSION < XEvents_Session_Name,varchar(1000),sql_text_and_errors >

ON SERVER

STATE = START

GO

----------------------------------------------------------------------------------------------

-- STEP: Wait for events to be recoredd

-- Ring Buffer Mode

SELECT

len( target_data ) as [Len Buffer]

FROM sys.dm_xe_session_targets st (nolock)

JOIN sys.dm_xe_sessions s (nolock) ON

s.address = st.event_session_address

WHERE

s.name = ''

-- Target File Mode

exec master.dbo.xp_cmdshell 'Dir "< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >*.*"'

GO

----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

--*********************************************************************************************

-----------------------------------------------------------------------------------------------

-- Retrieve XEvents output

SET NOCOUNT ON

DECLARE @outputfile varchar(500)='< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >' -- !!!! Edit your custom path here

---------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

--  Target File Mode

-- STEP: Get the Events captured - Note that the session can be still running & this can be repeated

IF (object_id( 'tempdb..#EventXML' ) IS NOT NULL) DROP TABLE #EventXML ;

DECLARE

@path NVARCHAR(260) = @outputfile+'*',

@mdpath NVARCHAR(260) = @outputfile+'*.xem',

@initial_file_name NVARCHAR(260) = NULL,

@initial_offset BIGINT = NULL

Select

Identity(int,1,1) as ID

,*

,cast

(

Replace(

E.event_data, char(3), '?'

) as xml

) as X

into #EventXML

FROM

master.sys.fn_xe_file_target_read_file (@path, @mdpath, @initial_file_name, @initial_offset) E

----------------------------------------------------------------------------------------------

-- STEP: Shred the XML for the above event types

IF (object_id( 'tempdb..#EventDetail' ) IS NOT NULL) DROP TABLE #EventDetail ;

--Shred the XML

SELECT

node.value('./@timestamp', 'datetime') AS event_time,

node.value('./@name', 'varchar(4000)') AS event_name

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./data)[5]', 'varchar(4000)')

ELSE NULL

END AS [Message]

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./data)[1]', 'int')

ELSE NULL

END AS Error_Value

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[7]', 'varchar(50)')

WHEN 'sp_statement_completed' THEN node.value('(./action)[1]', 'varchar(50)')

ELSE node.value('(./action)[2]', 'varchar(50)')

END AS activity_guid

,cast(null as int) as activity_sequence

,cast

(

CASE

WHEN node.value('./@name', 'varchar(4000)') IN ('sp_statement_starting', 'error_reported') THEN node.value('(./action)[1]', 'varchar(4000)')

ELSE NULL

END

as xml

) AS TSql_stack

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[2]', 'varchar(4000)')

ELSE NULL

END AS SQL_Text

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[3]', 'int')

ELSE NULL

END AS [DBID]

,cast(null as int) as ObjectID

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[4]', 'varchar(256)')

ELSE NULL

END AS [UserName]

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[5]', 'varchar(256)')

ELSE NULL

END AS [AppName]

,CASE node.value('./@name', 'varchar(4000)')

WHEN 'error_reported' THEN node.value('(./action)[6]', 'varchar(256)')

ELSE NULL

END AS [HostName]

,cast(null as varbinary(1000) ) AS handle

,cast( null as int) as offsetstart

,cast( null as int) as offsetend

,cast(null as varchar(4000) ) as Statement_Text

,cast( null as sysname) as [DatabaseName]

,cast(null as sysname) as [ObjectName]

,#EventXML.*

INTO #EventDetail

FROM #EventXML

CROSS APPLY #EventXML.x.nodes('//event') n (node)

-- Select count(*) as Events FROM #EventDetail

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- STEP: Separate Activity GUID from Sequence number - for sorting later on (should be combined w above step)

Update D Set

activity_sequence = CONVERT(int, RIGHT(activity_guid, LEN(activity_guid) - 37))

,activity_guid = CONVERT(uniqueidentifier, LEFT(activity_guid, 36))

FROM #EventDetail D

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- STEP: Extract handles & Offsets (should be combined w above step)

--Get the SQL handles

Update D Set

Handle = CONVERT(varbinary(1000), frame.node.value('@handle', 'varchar(1000)'), 1)

,offsetstart = frame.node.value('@offsetStart', 'int')

,offsetend = frame.node.value('@offsetEnd', 'int')

FROM #EventDetail D

OUTER APPLY D.tsql_stack.nodes('(/frame)[1]') frame (node)

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- STEP: For each handle, grab the SQL text (should be combined w single table above)

Update D Set

statement_text = Left( SUBSTRING(t.text, (IsNull(offsetstart,0)/2) + 1, ((case when IsNull(offsetend,0) > 0 then offsetend else 2*IsNull(len(t.text),0) end - IsNull(offsetstart,0))/2) + 1) , 4000 )

,[DatabaseName] = DB.Name

,objectid = T.objectid

FROM

#EventDetail D

cross APPLY sys.dm_exec_sql_text(D.handle) t

inner join master.sys.sysdatabases db (nolock) on db.dbid = D.dbid

-- select count(*) as Results from #EventDetail

----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

-- Dereference ObjectName

set nocount on

Declare @Tbl table ( Name sysname )

Declare @Stmt varchar(max)

Declare @DBName sysname

Declare @ObjectName sysname

Declare @ObjectId int

DECLARE curObj CURSOR

FOR Select Distinct [DatabaseName], objectid from #EventDetail where ObjectName is null and [DatabaseName] is not null

OPEN curObj

WHILE (1=1)

BEGIN

FETCH NEXT FROM curObj INTO @DBName, @ObjectId

      IF (@@fetch_status <> 0)

      break

      Set @Stmt = 'select Name from ' + @DBName + '.sys.sysobjects (nolock) where id = ' + convert( varchar(10), @ObjectId)

      Insert into @Tbl

      exec ( @Stmt )

      Set @ObjectName = null

      Select @ObjectName = Name from @Tbl

      Delete from @Tbl

      Update #EventDetail Set ObjectName = @ObjectName where [DatabaseName] = @DBName and objectid = @ObjectId

      END

CLOSE curObj

DEALLOCATE curObj

set nocount off

-- Show Details

Select

getdate() as now

,@@servername as Server_Name

,count(*) as [# of Errors]

,[message]

,[DatabaseName]

,[ObjectName]

,min([statement_text]) as Ex1_statement_text

,max([statement_text]) as Ex2_statement_text

,min([SQL_Text]) as Ex1_SQL_Text

,max([SQL_Text]) as Ex2_SQL_Text

--,[event_name]

,min([event_time]) as Min_event_time

,max([event_time]) as Max_event_time

,[Error_Value]

,[AppName]

,min([HostName]) as Ex1_HostName

,max([HostName]) as Ex2_HostName

,min([UserName]) as Ex1_UserName

,max([UserName]) as Ex2_UserName

from #EventDetail

where

IsNull([Error_Value],0) < 50000

group by

[DatabaseName]

,[ObjectName]

,[event_name]

,[Error_Value]

,[message]

,[AppName]

order by

[# of Errors] desc

 GO

Introducing SQL Server Extended Events