Xevent to azure storage (file target) keeps rolling over

Question

The number of files in my file target location keeps expanding very rapidly. Sometimes they are only ~10kb before the writing to a file stops and another file is created.

Below is an example of how I would create an xevent session that targets a storage account in azure. (Full steps can be found here:
https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-code-event-file)

CREATE EVENT SESSION [mycool1] ON DATABASE

ADD EVENT sqlserver.begin_tran_completed(

ACTION( mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.begin_tran_starting(

ACTION( mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.error_reported(

ACTION( mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([package0].greater_than_uint64 AND [package0].equal_boolean)),
ADD EVENT sqlserver.login(

ACTION( mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.rpc_completed(

ACTION( mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([package0].greater_than_uint64 AND [package0].equal_boolean)),
ADD EVENT sqlserver.sql_batch_completed(

ACTION( mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([package0].greater_than_uint64 AND [package0].equal_boolean))
ADD TARGET package0.event_file(SET filename=N'https://mystorage1.blob.core.windows.net/xevents/myfile.xel')

WITH (MAX_MEMORY=102400 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

After creating this, and interacting with SQL, I notice that my xevents files roll over extremely quickly without a reasonable explanation. Sometimes they grow to several MB and other times they are only a few KB.

I am not stopping and starting the session, it is running continuously.

Any pointers would be appreciated. I haven't noticed a pattern that explains when or why new files are created for xevent output.

Answer 1

It appeared to be some sort of internal misbehavior/bug in the xevents system. I didn't dig that deep but noticed that I would get a memory-related error message in SSMS if I tried to create another xevent session. The error said that SQL it didn't like my "MAX_MEMORY" setting which was set to 100 MB, as you can see in my original example above.

When I dropped that down to 10 MB then I stopped seeing all the unusual behavior. The xevent will continue to write to the same file for an extended period of time rather than constantly rolling over after writing just a few KB to each file.. I also started using max_file_size = 100 and max_rollover_files = 20 to limit the amount of space that this consumes in the azure storage account.

I suspect there was some internal error going on. When this xevents stuff runs locally in our datacenter, the first place I would check for a problem is the event log. Is there a similar location for errors in Azure SQL Database? It would be nice to know when the database is not happy with itself for some reason. I don't see anything popping out at me in the portal, but I'm new to Azure so I'm not sure I would know where to look. Or maybe there is yet another xevent I need to subscribe to, in order to get error messages about internal errors in xevents?

Answer 2

Good day,

You can use the parameter MAX_ROLLOVER_FILES to limit the number of files. Remember that using rollover, the old files are deleted so you need to make sure that you don't need the data there.