8 Replies Latest reply on Jan 14, 2021 1:04 AM by Andreas Bühler

    P360 v10.0 database tempdb does not stop growing in filesize

    Arjen Martens New Member
    Arjen Martens Jan 5, 2021 8:19 PM

      Hi all,

       

      This question is related to P360 v10.0 and its tempdb database running on SQL Server release 17.

       

      Observation/Issue:

      Based on the below mentioned setup, we are experiencing disk space issues on the P360 database server due to the tempdb database (files) growing infinitely big.

      In detail this means that we have monitored that the tempdb database will grow with roughly 1,5GB each day while not releasing any space in the meantime.

      We have had occurences where the tempdb reached 40GB in size and therefore consuming all disk space on the server's C: drive and subsequently blocking P360 activities.

       

      A database restart or P360 service restart is usually the only way to free up all tempdb space again, whilst this is really not how it should be we reckon.

       

      Hence our questions therefore are:

      1. Is it normal that P360 tempdb consumes 1,5GB diskspace each day (even during holiday period)?

      2. Is it normal that the P360 tempdb keeps growing infinitely? If so, how to treat this?

       

       

      Current setup:

      • P360 v10.0 database server is running on an AWS server with 8 CPU having 2 disk drives with C: 80GB and D: 150GB and utilizing release SQL Server 2017.
      • Due to having 8 CPU, we have created 8 tempdb files on the C: drive with an initial size of 5120MB each and an autogrow of 2048MB each.
      • The actual P360 data sits in the 3 database files on the D: drive.
      • The recovery model of the tempdb database is set to SIMPLE.
      • We are not using Media Manager, Supplier Portal, Web Search and additionally Audit Trail and BPM (ActiveVOS) databases sit on other SQL servers.

       

      Thanks in advance for your help!

        • 1. Re: P360 v10.0 database tempdb does not stop growing in filesize
          Ryan Jenson Seasoned Veteran
          Ryan Jenson Jan 6, 2021 12:53 PM (in response to Arjen Martens)

          Hi Arjen,

           

          I've experienced the same issue on our PIM SQL servers (dev and production, although it's more prevalent on production). SQL tempdb files will be emptied out upon restart of the MS SQL service but this isn't always an optimal solution.

           

          There are some settings changes that can help prevent tempdb inflation:

          (assuming GUI use)

          1. In "[Right click] > Properties > Options > Recovery model" make sure that "Simple" is selected

          2. In "[Right click] > Properties > Files" you should be able to review the auto-sizing increments and limits. You may want to investigate the auto-sizing that best fits your needs.

           

          Attached I have a script of simple SQL functions that aid in exploring the situation further. PLEASE REVIEW THE FUNCTIONS BEFORE RUNNING ANY OF THEM!

           

          Normally it is considered bad practice to outright shrink a SQL database due to the vicious cycle of having to re-index which then expands the file size and, thus, having to shrink again. Tempdbs are different however in that this rule isn't exactly applicable. I have had success shrinking the tempdb database, files, and logs without consequence (so long as a large task isn't currently running).

           

          To directly answer your questions:

          1. It is neither normal nor abnormal, it's not uncommon to happen from time to time but it should not be a frequent occurrence.

          2. Infinite database growth (for any of the databases) is a setting that can be changed. If a database is growing infinitely you may just have the default SQL settings active.

           

          Hopefully some of these pointers were able to help. It would be best for you to discuss any actions with your SQL Administrator before attempting. From what I know of PIM SQL; there aren't any specific precautions regarding the tempdb aside from standard SQL best practices.

          • 2. Re: P360 v10.0 database tempdb does not stop growing in filesize
            Arjen Martens New Member
            Arjen Martens Jan 7, 2021 7:46 AM (in response to Ryan Jenson)

            Thanks for your swift reply Ryan, much appreciated!

             

            Kind of "glad" to see that you recognize these challenges, at least to have some common ground.

             

            The settings you have been proposing are known to us and used, however we reckon we could turn off autogrow indeed to see what happens when tempdb hits it filesize limit. At the moment SQL server thinks it can autogrow with 2048MB a file until infinity and it tries to do so while the remaning disk space is actually insufficient. Hence it might work when we up front tell the system that it cannot autogrow and hence forcing it to drop records for new records instead -- so thanks for the insights into this experiment already.

             

            Also thanks for your scripts attached, we will scan through these with care!

             

             

            At some point in time we were wondering whether we had to make schedule a bat file to stop-start P360 server service instead of the SQL database, but even that could cause some real troubles when automated....

             

             

            We will try out some stuff and revert, thanks again.

             

            Any other tips from other SMEs out here are still welcome ofcourse!

            • 3. Re: P360 v10.0 database tempdb does not stop growing in filesize
              Andreas Bühler Guru
              Andreas Bühler Jan 11, 2021 4:50 AM (in response to Arjen Martens)

              Hi Arjen, Hi Ryan,

              we're currently investigating this issue, if not already done, please open a support case with our support team.

              Arjen, please add any exception / error message to the support ticket. Especially with the limited auto grow.

               

              The internal issue number is HPM-54341 just in case you want to mention it to the support representatives

              Be assured we're working on this issue top priority!

               

              Can you also reproduce it on a dev or qa system?

               

              Thank you

              Andreas

              • 4. Re: P360 v10.0 database tempdb does not stop growing in filesize
                sai manoj pappu New Member
                sai manoj pappu Jan 11, 2021 9:20 AM (in response to Arjen Martens)

                Hi Arjen,

                 

                Can you also share us the MS SQL version please.

                 

                Thanks,
                Sai Manoj.

                • 5. Re: P360 v10.0 database tempdb does not stop growing in filesize
                  Arjen Martens New Member
                  Arjen Martens Jan 13, 2021 8:05 AM (in response to sai manoj pappu)

                  Hi Sai Manoj,

                   

                  MSSQL14

                  and

                  SSMS v17.2

                  • 6. Re: P360 v10.0 database tempdb does not stop growing in filesize
                    Arjen Martens New Member
                    Arjen Martens Jan 13, 2021 8:09 AM (in response to Andreas Bühler)

                    Hi abuehler,

                     

                    Thanks for your note of importance!

                     

                    We will be opening a support case on your request, whilst we cannot provide any error messages or so since there is none. This seems to be normal operation until the disk is full, with all the effects that brings with it.

                     

                    We cannot reproduce this directly on any other environment (DEV,TST,UAT) since these environments are not under daily load of data maintenance activities like PROD env is..

                     

                    Thanks again!

                     

                     

                    P.S.

                     

                    We reckon that setting up server-side cleanup jobs for PIM server processes and soft deletes could maybe help to reduce the stress on the DB, but not sure whether this will improve or worsen the tempdb utilization..

                    • 7. Re: P360 v10.0 database tempdb does not stop growing in filesize
                      Andreas Bühler Guru
                      Andreas Bühler Jan 14, 2021 1:03 AM (in response to Arjen Martens)

                      We were more looking for the full version number, including the hotfixes of sql server.

                      However, we most likely found the reason for the issue and will provide corresponding ebf's once confirmed.

                      The support team will keep you updated through the support ticket.

                      • 8. Re: P360 v10.0 database tempdb does not stop growing in filesize
                        Andreas Bühler Guru
                        Andreas Bühler Jan 14, 2021 1:04 AM (in response to Arjen Martens)

                        Setting up the cleanup jobs is always a good idea actually - but it's not related to this issue.