Sql-server – File growth and location change for system health extended events

extended-eventssql serversql server 2014sql-server-2012sql-server-2016

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

Best Answer

  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.

Related Question