How are the system health extended events files rolling over?












4















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?










share|improve this question





























    4















    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?










    share|improve this question



























      4












      4








      4








      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?










      share|improve this question
















      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?







      sql-server sql-server-2012 sql-server-2014 extended-events






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 1 hour ago







      BeginnerDBA

















      asked 5 hours ago









      BeginnerDBABeginnerDBA

      5441417




      5441417






















          1 Answer
          1






          active

          oldest

          votes


















          5















          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:



          screenshot of Windows Explorer showing system_health XE logs



          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.






          share|improve this answer


























          • 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













          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
          });


          }
          });














          draft saved

          draft discarded


















          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









          5















          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:



          screenshot of Windows Explorer showing system_health XE logs



          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.






          share|improve this answer


























          • 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


















          5















          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:



          screenshot of Windows Explorer showing system_health XE logs



          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.






          share|improve this answer


























          • 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
















          5












          5








          5








          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:



          screenshot of Windows Explorer showing system_health XE logs



          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.






          share|improve this answer
















          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:



          screenshot of Windows Explorer showing system_health XE logs



          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.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          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





















          • 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




















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          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





















































          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







          Popular posts from this blog

          How to label and detect the document text images

          Vallis Paradisi

          Tabula Rosettana