How are the system health extended events files rolling over?
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?
sql-server sql-server-2012 sql-server-2014 extended-events
add a comment |
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?
sql-server sql-server-2012 sql-server-2014 extended-events
add a comment |
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?
sql-server sql-server-2012 sql-server-2014 extended-events
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?
sql-server sql-server-2012 sql-server-2014 extended-events
sql-server sql-server-2012 sql-server-2014 extended-events
edited 1 hour ago
BeginnerDBA
asked 5 hours ago
BeginnerDBABeginnerDBA
5441417
5441417
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230689%2fhow-are-the-system-health-extended-events-files-rolling-over%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
add a comment |
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.
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
add a comment |
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.
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.
edited 3 hours ago
MDCCL
6,84331745
6,84331745
answered 5 hours ago
jadarnel27jadarnel27
5,85411938
5,85411938
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
add a comment |
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
Thanks, i will test those numbers and combination. I edited my question with doubt on files being rolled over. Any idea how to achieve that?
– BeginnerDBA
4 hours ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@ Thanks for the answer. Just came up with question on how these files are filling up. if you have any idea?
– BeginnerDBA
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
@BeginnerDBA You're welcome! I'm glad I could help =) That seems like a fairly independent question. You should post it as a new question rather than adding it to this one.
– jadarnel27
1 hour ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230689%2fhow-are-the-system-health-extended-events-files-rolling-over%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown