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
- Event is not restarted
- 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?