Extended Event to track and trace syntax errors

  • Article

This might sounds like a strange requirement, but it can be converted to do a number of different things. The scenario is that for reason I won't discuss here, I want to track any user / application which issues an SQL batch which contains invalid syntax. The value here is that this would be extremely difficult to do on this system with tools like profiler due to the large volumes going through the server. This XEVENT will happily sit and monitor all batches for the error codes 102, 447 and 448 which are various derivatives of invalid syntax or invalid language usage. The output is written to a binary file which can then be queried using standard TSQL / XQuery. Alternatively if you don't like the scripting part of this you could look to use the Extended Events SSMS Add in from code plex.

https://extendedeventmanager.codeplex.com/

and here is the piece of code

-- XEvent Session looking for errors 447,448 and 102
-- log examples to files so can be correlated back to netmon traces

if exists(select * from sys.server_event_sessions where name='SharePoint_Errors')
DROP EVENT SESSION SharePoint_Errors ON SERVER
go

CREATE EVENT SESSION SharePoint_Errors ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
sqlserver.session_id,
sqlserver.tsql_stack,
sqlserver.sql_text,
sqlserver.client_hostname,
sqlserver.client_pid
)
WHERE error = 102
OR error = 447
OR error = 448
)
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\temp\SharePoint_Errors.xet', metadatafile='c:\temp\SharePoint_Errors.xem')
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF)

go

ALTER EVENT SESSION SharePoint_Errors ON SERVER
state=start
go

--stop the session if necessary
--ALTER EVENT SESSION SharePoint_Errors ON SERVER
--state=stop

-- Cleanup the session
--drop EVENT SESSION SharePoint_Errors ON SERVER

********
and here is the sample script to query the output files
********

--get values from XET binary files

--straight select, view the XML manually
select CAST(event_data as XML) , *
from sys.fn_xe_file_target_read_file
(
'C:\temp\SharePoint_Errors_0_129291097895820000.xet',
'C:\temp\SharePoint_Errors_0_129291097896520000.xem',
null,
null
)

--details

SELECT --*
xdata.value('(/event/data[3]/@name)[1]', 'varchar(100)') AS [name],
xdata.value('(/event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS [name]

--insert other XML nodes from the output as required here
--you can then add condition checks on their values as well
FROM
(
select CAST(event_data as XML)
from sys.fn_xe_file_target_read_file
(
'C:\temp\SharePoint_Errors_0_129291097895820000.xet',
'C:\temp\SharePoint_Errors_0_129291097896520000.xem',
null,
null
)
) as xmlr(xdata)

 
*********
remember to change the file unique identifiers to those of your output files
*********