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)
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
