SQL Server troubleshooting thoughts from Graham Kent, (ex) Senior Support Escalation Engineer at Microsoft Sweden.
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.
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 SERVERgo
CREATE EVENT SESSION SharePoint_Errors ON SERVERADD EVENT sqlserver.error_reported (ACTION (sqlserver.session_id,sqlserver.tsql_stack, sqlserver.sql_text,sqlserver.client_hostname,sqlserver.client_pid)WHERE error = 102OR error = 447OR 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)
ALTER EVENT SESSION SharePoint_Errors ON SERVERstate=startgo
--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 manuallyselect 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)
SELECT --*xdata.value('(/event/data/@name)', 'varchar(100)') AS [name],xdata.value('(/event/action[@name="sql_text"]/value)', 'varchar(max)') AS [name]
--insert other XML nodes from the output as required here--you can then add condition checks on their values as wellFROM ( 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*********