3 Replies Latest reply on Nov 29, 2016 7:28 PM by RichardLetts

    Upgrades/Updates Change DB Autogrowth & Max Size settings

    angela.henry

      Every time we upgrade/update the application/database, it sets the autogrowth to 100MB and sets the max size to unlimited for both data and log files.  This is a huge problem.  Our database is 395GB.  Can you imagine the fragmentation/performance issues if we kept those settings?  This is a huge problem and has been ever since we bought this module three years ago.

       

      Can we get a fix for this?

        • Re: Upgrades/Updates Change DB Autogrowth & Max Size settings
          floydgm

          We just went through a similar issue however our database was 1.4 TB at the start on a 2 TB drive, which became a serious issue in our upgrade to v12.0. The configuration wizard was running a very large query on the database and the drive would run out of space due to all the transaction logs generated. We found that the query was removing 709 million out of 719 million rows of data which was what filling up the transaction logs. The upgrade would then fail due to lack of space on the SQL Server drive.

          To resolve:

          1: On our SQL server Added an additional 2 TB drive solely for the DB backups and logs

          2: Generated a query that instead of removing 709 million rows - removed 100K rows at a time, the script looped until completed. Once that was completed we were able to shrink the database. Which I think put the DB down to about 684 G.

          Then we were able to run the configuration wizard for the upgrade with no issues. Not that this is your issue but  I have included the manual query script we used below:

           

          SET NOCOUNT ON;

           

          DECLARE @r INT;

           

          SET @r = 1;

           

          WHILE @r > 0

          BEGIN

            BEGIN TRANSACTION;

           

          DELETE TOP (100000) FROM [dbo].[PendingNotifications]  

          WHERE Subscription_Id IN (        SELECT Id FROM [dbo].[Subscriptions]      

          WHERE EndpointAddress NOT LIKE 'http://%'       

          AND EndpointAddress NOT LIKE 'https://%'    );       

           

            SET @r = @@ROWCOUNT;

           

            COMMIT TRANSACTION;

           

            -- BACKUP LOG ... -- if
          full

          END

            • Re: Upgrades/Updates Change DB Autogrowth & Max Size settings
              RichardLetts

              That table is empty in our install, and the only time its ever had some rows in it is pre-12.x when the UDT plugin was crashing the module engine every couple of hours.

               

              What is more...

              the query removes pending notifications for any subscriptions that do not start with either https or https

              post-upgrade there are no subscriptions that start with either https or https

               

              Which somewhat implies that truncating the table would be a LOT faster...

            • Re: Upgrades/Updates Change DB Autogrowth & Max Size settings
              sqlrockstar

              Angela,

               

              Sorry to hear about this, let me reach out to someone on the NPM team to understand more about the issue.

               

              Tom