I have created an extended events session to capture login and logout information. The session is setup to store events to a file. I understood that one of the advantages of using an event file (rather than a ring buffer) was that events history would be persistent across a restart. However it appears that a new event file is created when SQL Server is restarted and the history is lost. Is this expected? If so, what is the advantage of using a file rather than a ring buffer?
Based on your question (and additional comments regarding your setting of max_rollover_files
to 1:
However it appears that a new event file is created when SQL Server is restarted and the history is lost. Is this expected?
Yes - that is expected. When you restarted SQL Server all currently running extended events were terminated. When you restarted SQL Server, a new extended event session started and your existing target file was replaced because you indicated you only wanted 1 file. If you want to preserve extended event data stored in target files across server restarts, you will have to increase the max_rollover_files
to something greater than 1.
sys.fn_xe_file_target_read_file (path, mdpath, initial_file_name, initial_offset)
has the capability of reading all extended event files in a the path referenced by mdpath if you specify null
for the initial_file_name, so maintaining multple files should not be a problem.
Regarding the use of the Ring Buffer, take a look at why Jonathan Kehayias (world renowned expert in SQL Server Extended Events) Hates the ring buffer.
-
Thanks for the quick response. I'll set max_rollover_files to a larger number and look at
sys.fn_xe_file_target_read_file
to read from multiple files. I think the last bit is the piece I was missing. I think I had previously read Jonathan's article and had avoided using Ring Buffer for one or more of the reasons he described. – Dave Potts Oct 26 '17 at 15:32
max_rollover_files
setting? – SqlWorldWide Oct 26 '17 at 12:22max_rollover_files
setting and keep that many files. – SqlWorldWide Oct 26 '17 at 14:22