0

I am continuing the questions over system health extended event from my previous question How are the system health extended events files rolling over?

  1. I see system health file location by default goes to log folder, can this be changed as its very hard to find for 200+ servers and thus making it a standard location across x servers

  2. Need to understand below behavior per my comments on previous question when number of files were increased from default of 5 to 10 and size from 5 MB to 25 MB

a) Why don't we see additional 5 files yet listed once we change number of files to 10?

b) How are the files filling up the space as I see there is no sequential filing say oldest files [ 3 days old] are still 3 MB while some of latest ones [ consider today] have already filled with 5 MB and going back to old ones to record data. How is this writing mechanism done? I need to understand this to figure out how many files and how big can they be created before making a change

2
  1. I see system health file location by default goes to log folder, can this be changed as its very hard to find for 200+ servers and thus making it a standard location across x servers

Yes, you'll have to edit the extended event definition:

ALTER EVENT SESSION [system_health] ON SERVER 
DROP TARGET package0.event_file
ALTER EVENT SESSION [system_health] ON SERVER 
ADD TARGET package0.event_file(SET filename=N'F:\MyLocation\system_health.xel',max_file_size=(50),max_rollover_files=(40))
GO

a) Why don't we see additional 5 files yet listed once we change number of files to 10?

Because it writes to the files in a rolling fashion, like SQL Server Errorlogs... so you can set it to keep 99 errorlogs but it won't just go out and make 99. When the data rolls over, a restart occurs, etc., then the new files will be created if needed.

b) How are the files filling up the space as I see there is no sequential filing say oldest files [ 3 days old] are still 3 MB while some of latest ones [ consider today] have already filled with 5 MB and going back to old ones to record data.

They are used as needed but never "go back" and fill the files up that were previously used. The old ones will eventually be aged out and deleted with a new file created in place of the old deleted one. So yes, everything you're seeing is by design. It means the 3MB one had 3MB of data written to it before something happened (most like a restart of the service) and then the next one had 5MB written to it and rolled over to a new file.

How is this writing mechanism done?

If it's set for a delay or synchronous it'll wait that long before flushing, otherwise it flushes the buffer out to the file. Not sure what you're looking for with this question.

I need to understand this to figure out how many files and how big can they be created before making a change

It's going to be different for each instance, though most instances I would hope stay up and running a long time between restarts... if so, most should fall in line with an average amount of testing to keep whatever data you're looking at collecting for however long you're looking to keep it.

  • Thanks Sean, looks perfect. I will try to understand if all 10 files are created in case we do a restart of extended event or scenario where server or SQL service gets restarted – BeginnerDBA Feb 28 at 18:45
  • @BeginnerDBA It still won't create them all, you'd need to restart 9 times for all 10 files to be created. If you have a dev environment where you can simulate this, that'd be the place to test :) – Sean Gallardy Feb 28 at 19:22
  • Thanks, yes i do have. Will test for out of curiosity and knowledge :) – BeginnerDBA Feb 28 at 20:02

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.