- 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.
