How to flush event data to event file (.xel) in sql server extended event

Question

I am moving from SQL profiler to extended event.

I encounter one issue that all queries are not captured by extended event if I use target as event file, but all queries get capture if I use ring buffer as target. after searching on google I found that event file uses some buffer and only after buffer is full it writes data to file. but I need to stop the event capture and use the file somewhere else and every time I stop session data which is in buffer lost. How can I flush buffer to file? before I stop session. The sample test event I created is like below. (I remove ring buffer and use event file)

I tried to reduce MAX_DISPATCH_LATENCY and MAX_MEMORY but nothing works.

CREATE EVENT SESSION [Test] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[database_name],N'pubs'))),
ADD EVENT sqlserver.sql_batch_starting(SET collect_batch_text=(1)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[database_name],N'pubs')))
ADD TARGET package0.event_file(SET filename=N'C:\TEMP\Test.xel')
--ADD TARGET package0.ring_buffer(SET max_events_limit=(10000),max_memory=(1048576))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Answer 1

Hi Vivek,

When using the event_file target in SQL Server Extended Events, the data is flushed to the event file based on certain conditions:

Buffer Full: When the internal buffer allocated for the event session is full, the data is automatically flushed to the event file.

Session Stop: When the event session is stopped, any remaining data in the buffer is flushed to the event file.

Manual Flush: You can manually flush the data to the event file using the ALTER EVENT SESSION statement with the FLUSH option MAX_DISPATCH_LATENCY.

Did you mean that setting the MAX_DISPATCH_LATENCY to 1 did not work?

---

If the answer is helpful,  please click "Accept Answer" and kindly upvote it.