Open Forum

Expand all | Collapse all

Forecaster SQL settings

  • 1.  Forecaster SQL settings

    Posted Feb 14, 2020 11:30 AM
    Good day,

    In the last 2 months, our Forecaster databases (we have 12) have twice doubled in size overnight as SQL has added free space to the DBs.  It has taken free disk space from ~40% to 5%

    Has anyone run into this before?  What would kick off that kind of growth?

    I plan to adjust auto-growth and to limit database size so that, at a minimum, the drive will not fill.

    Thoughts and suggestions appreciated.

    Heidi

    ------------------------------
    Heidi Farnham
    Dungarvin
    Mendota Heights MN
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Forecaster SQL settings

    GPUG ALL STAR
    Posted Feb 19, 2020 11:16 AM
    Hi @Heidi Farnham
    I doubt there are still much users around that use Forecaster.. my best wild guess would be that someone ran some extended planning in Forecaster which in turn generated a lot of data in the tables, that's the only valid reason I'd see for the sudden size increase in your Databases.
    It's hard to tell without having a close picture of the whole system setup, especially on the SQL side where someone may have played with the DB growth settings.
    Maybe someone from the MSX Group (like @Derek Krebs ​​​or @Nick Sercer ) may able to provide a more detailed answer.​

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Forecaster SQL settings

    TOP CONTRIBUTOR
    Posted Feb 19, 2020 11:51 AM
      |   view attached
    Hello, attached is a document which will help with recommendations and updates for database size and speed as we support hundreds of Forecaster customers today as that product works with all the latest SQL and O/S versions.  Feel free to email if any follow up questions and extra tips and guides I could share along with the Forecaster (and MgtRpt and FRx) training & user guides.  Happy reporting & budgeting!

    ------------------------------
    Derek Krebs
    Sr Consultant
    MSX Group
    derek.krebs@msxgroup.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Forecaster SQL settings

    Posted Feb 19, 2020 12:36 PM
    Beat,

    Thanks for your reply.  We are moving off FC 1st or maybe 2ndquarter.  It was just odd that 5 of the 15 companies we have grew all at once, then the next night one more doubled in size.

    SQL growth settings are default.  I added disk space and will shrink the DBs next maintenance windows and am hoping they hold until FC retirement.

    Heidi

    ------------------------------
    Heidi Farnham
    Dungarvin
    Mendota Heights MN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Forecaster SQL settings

    GPUG ALL STAR
    Posted Feb 19, 2020 12:48 PM
    That's good to hear that FC is retiring :-)
    I've looked thru Derek's document and there are numbers of suggestions that could apply in your case, like the Zap of the DB which clears up some data in FC. Then doing a SQL shrink might temporarily get you back to the previous size, though depending on the source of the growth, it might not last very long.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Forecaster SQL settings

    SILVER CONTRIBUTOR
    Posted Feb 20, 2020 12:08 PM
      |   view attached
    Hi @Heidi Farnham - In addition to shrinking the DBs (which should be done by shrinking the files as opposed to shrinking the DB), make sure the 'Autogrowth' setting of the database files (both data and log files) are set to grow in MB rather than by a percent.

    The default for the 'model' database, which controls the default settings for most new databases is to autogrow by a percent of the file size. Note that databases created through the GP 'Create Company' process have growth rates hard-coded in the Microsoft code to grow by 20 and 25 percent (Data and Log files respectively).

    ​If a database is relatively small 20-25% autogrow may not be a big deal, but when your database gets larger and/or a DB is in FULL recovery model and you aren't taking Log backups, you could experience diskspace issues in a hurry.

    When setting Autogrowth to grow in MB as shown below, don't use the default - make it a reasonable amount based on the actual growth rate of the database. If the database needs 100MB of space and autogrow is set to 10MB, then SQL has to perform that action 10 times, so you'd want to set autogrow to 100-200MB. A good way to see the actual growth of your your databases over time is to query the msdb database for backup sizes over time - I've attached a sample script (change .txt to .sql) to help with that.

    Database Properties - Files
    DB Autogrowth settings
    Hope this helps!


    ------------------------------
    Jeff Pfershy
    Sr Business Analyst
    BluJay Solutions Inc
    Holland MI
    ------------------------------

    Attachment(s)

    txt
    Backup History.txt   2K 1 version
    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role