Updating a SQL Server Extended Event session to add data storage – is it possible?

All we need is an easy explanation of the problem, so here it is.

I have an extended event session to track deadlocks, and have the data storage set up for Event_File, max file size of 20 MB, max number of files is 5, enable file rollover is true.

It looks like I can’t change the data storage, even after I stop the session. It’s all grayed out. I need to be able to increase the number of files, because for some reason it’s not actually going to the max file size of 20 MB. I have 4 files already for this session, and the largest size is 47 KB.

How can I increase this? Do I just need to re-create the session? I already have history in it that I want to keep.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

To keep the history just stop the session and copy all the event files for that session to another place. You can query them like the example provided by Dan Guzman’s answer. You can query those files even if the event session that created them no longer exists.

About the files never reaching the specified size, it might be related to the chosen size, as you can see on the Event File Target doc:

When max_file_size is set to a size larger than the size of the
session buffers, it may be rounded down to the nearest multiple of the
session buffer size. This may create a target file that is smaller
than the specified value of max_file_size. For example, if the buffer
size is 100MB and max_file_size is set to 150MB, the resultant file
size is rounded down to 100MB because a second buffer would not fit in
the remaining 50MB of space.

Also, by stopping and starting a session a new file is created even though the limit size has not been filled on the previous file.

To alter the max_file_size of an existing XE Session (let’s say the one from the answer I linked above) it would be something like this:

ALTER EVENT SESSION [errors] ON SERVER
DROP TARGET package0.event_file;

ALTER EVENT SESSION [errors] ON SERVER
ADD TARGET package0.event_file(
    SET 
        filename=N'C:\TraceFiles\errors',
        max_rollover_files=(5),
        max_file_size=(512)
    );

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply