Tjyylli

I see the default system health session events within SQL Server are 5 MB each with 4 files in total.

We are trying to fetch data from the system health in historical events but for some critical production servers they just keep rolling over very fast. For some they stay there even for weeks

How is the mechanism working in SQL Server of rolling over the files and is there a way I can check when and how this is happening?

Edit@ Per Answer

1. Event is not restarted


2. Server is also not restarted and it happens per scheduled once a month

Therefore i am thinking there could be possibly lot of unwanted events being captured in system health. How can i find them and filter them out in this system health ?

Also, should we be increasing file sizes say 200 MB 4 files or just keep small size files like 10 MB 80 files? Which one is better?

Update@ I've already marked this an answer, but while checking another thing that popped out:

The files are not filling in sequential order ; say i increased to 10 files to 10 MB each. we see 1-6 files couple of days old seems all filled with 10 MB, while file 7 and 8 for the same day is 2 MB and file 9 and 10 filled up completely 10 MB. I guess this is expected, just curious why it's like this?

How is the mechanism working in SQL Server of rolling over the files

The files will roll over when any of the following occurs:

• the file reaches its max size (in this case, 5 MB)


• the XE session is stopped and restarted for any reason

This could be someone actually running the command:

ALTER EVENT SESSION [session_name]  

ON SERVER  

STATE = STOP;

Or it could be that the server or SQL Server instance was restarted (this seems unlikely given you mentioned this is a critical production server).

and is there a way I can check when and how this is happening?

If all of your files are at or around 5 MB, your server is just generating a lot of events. I have a production server that's not very busy, and it still takes less than 24 hours to fill up one of the 4 XE files with 5 MB of events. Here's a screenshot, check out the modified dates on those files:

To see what events are included in the event session in SSMS, you can go to Management → Extended Events → Sessions → right-click "system_health" and choose "Script Session as" → "CREATE to" → "New Query Editor Window".

However, note this from the Microsoft docs article Use the system_health Session:

We recommend that you do not stop, alter, or delete the system health session.

So your best bet would be to create a new XE session with just the subset of events you want, and the specific Event File Target settings that work for your system.

If the files are less than 5 MB, then the server or event session is being restarted. You can check the SQL Server error log for when restarts are happening, and you'll have to check with those who have CONTROL SERVER permissions to see who is stopping the XE session if that's the case.

Also, should we be increasing file sizes say 200 MB 4 files or just keep small size files like 10 MB 80 files? Which one is better?

It depends. Querying (reading) from a bunch of different files can be inconvenient. So I'd lean towards a lower total number of files, and just increasing the size of each one.

That being said, I've never tried parsing through a really large XE file, so there might be issues with that. You'll have to test and see which one is better for your system.