Sql-server – How to prevent Extended Events from tracking system fired events

extended-eventssql server

I have employed Extended Events to track the DML and DDL operations that are being performed on the tables within a particular database. Apart from this I'm also tracking the procedures that a user(for now I'm the sole user) executes within the database which is being tracked. I'm using the event 'sql_statement_completed' for the same. It captures all the sql statements that are successfully completed, post which I filter the statements to store only Insert, Update, Delete and Drop statements (exec statements are also being stored). Below is the code pertaining to it:

CREATE EVENT SESSION <track> ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_hostname,
           sqlserver.sql_text,
           sqlserver.username)
    WHERE ([sqlserver].[database_name]= <database_name> 
    AND [sqlserver].[is_system]=(0) 
    AND (([statement] like 'INSERT%' 
       OR [statement] like 'UPDATE%' 
       OR [statement] like 'DELETE%' 
       OR [statement] like 'DROP%' 
       OR [statement] like 'ALTER%' 
       OR [statement] like 'EXEC%')))) 
ADD TARGET package0.event_file(
    SET filename=N'C:\ExtendedEvents\track.xel',
    max_file_size=(102400),
    max_rollover_files=(10000),
    metadatafile=N'C:\ExtendedEvents\track.xem')
WITH (MAX_MEMORY=1048576 KB,
      EVENT_RETENTION_MODE=NO_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=5 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=ON)

Now while this has been tracking the DML and DDL statements that are being executed against the tables in the database, it has also been tracking some statements that have not been executed by the user directly(particularly some procedural statements that seem to have been executed by the system itself).

The pic below subsumes the statements(not directly fired the user) that have been tracked:

enter image description here

This is despite the fact that I have explicitly set [sqlserver].[is_system]=(0) in the where predicate of Extended Events, to disable tracking of system fired statements and only track the user fired statements.

So why is it tracking system fired statements as shown in the pic above?
How to circumvent the same?

Best Answer

These statements look to be activity generated by SQL Server tools, management API, or a third party product. This activity happens on regular connections, not a system session which the is_system field identifies.

You might be able to add a filter on client_app_name to exclude the unwanted activity unless some of the activity from that application or tool needs to be captured.

Related Question