1

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?

|improve this question
  • What is your max_rollover_files setting? – SqlWorldWide Oct 26 '17 at 12:22
  • It is set to 1 because I want to keep all the data in one file. I know that limits the number of events I can store but the file is being truncated before it reaches the size limit. – Dave Potts Oct 26 '17 at 14:17
  • I just tested this and it will not work. With restart SQL Server will still look at max_rollover_files setting and keep that many files. – SqlWorldWide Oct 26 '17 at 14:22
3

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.

|improve this answer
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.