Sql-server – Updating a SQL Server Extended Event session to add data storage – is it possible

extended-eventssql server

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.

Best Answer

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)
    );
Related Question