size

Horse06Horse06 Member Posts: 496
Our database used size now has reached 53% and I optimize from time and time. The manual mentions it will not exceed 80%. otherwise it will be expanded. But I looked at the file menu and cannot find the expand. When I created a database in my client computer, I can see it. Any advice? Thanks!

Comments

  • SavatageSavatage Member Posts: 7,142
    sql has an auto expand but I have read that it is not wise to have it automatically expand because it could happen when you least want it to, during the middle of the day when people are working.
    manual mentions it will not exceed 80%

    Will not or Should not - very different

    File->Database->Expand
  • DenSterDenSter Member Posts: 8,307
    I've heard rumors that size doesn't matter, but I suspect that's not true :mrgreen:

    Neither the native database server nor SQL Server has a mechanism that automatically expands the database when used percentage hits 80%. On native it is recommended to have at least 15-20% free space, and the only way to expand the database is manually. On SQL Server it's the file's properties that determines whether the file automatically expandes, and by how much the file expands. It doesn't do that though until the file is full. So at some point the file is 99.9999999% full, a user posts another transaction, file is full, SQL Server will automatically expand the file by the percentage setup in the file properties.

    You will want to set the file to big enough not to automatically expand too often, and keep an eye out and manually expand the file during off hours. You should keep the auto-grow setting on though, so that the database doesn't shut down when it hits that spot.
  • bbrownbbrown Member Posts: 3,268
    The rule I use is to maintain the database between 60% and 80% full. When the database reaches 80 expand down to 60% full. Do not rely on auto-grow except for a safety net. Auto-grow occurs a 100% full. Not at 90%, 95%, 99%, or 99.99%. This system does NOTHING else while it expands the files. This applies to both data and log files.

    This is for SQL. But I apply the space rules to native also
    There are no bugs - only undocumented features.
  • Ian_Piddington10199Ian_Piddington10199 Member Posts: 167
    I have also found that with SQL its safer to tell it to expand by a set size than by a percentage.
    Regards

    Ian
  • bbrownbbrown Member Posts: 3,268
    I have also found that with SQL its safer to tell it to expand by a set size than by a percentage.

    Bottom line is - Do not rely on Auto-Grow.

    Percent or size the issue is still the same.
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,307
    bbrown wrote:
    The rule I use is to maintain the database between 60% and 80% full. When the database reaches 80 expand down to 60% full.
    That's a good rule, and the lesson learned there is that you go in and check the file size periodically to make sure the files are still good. There might even be a way to have SQL Server send you an email when it reaches 80%.
  • Horse06Horse06 Member Posts: 496
    Thanks for everone! One year ago, I read the post by Savatage, but for some reason I just read a few lines. Could anyone send me a link.

    BTW, I cannot find expand from database of the file menu. I am wondering if we don't have licence or some other reasons. Thanks a lot!
  • bbrownbbrown Member Posts: 3,268
    Horse06 wrote:
    Thanks for everone! One year ago, I read the post by Savatage, but for some reason I just read a few lines. Could anyone send me a link.

    BTW, I cannot find expand from database of the file menu. I am wondering if we don't have licence or some other reasons. Thanks a lot!

    For SQL (Database - File - Alter). Just change the file size and click OK
    There are no bugs - only undocumented features.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    bbrown wrote:
    ...

    For SQL (Database - File - Alter). Just change the file size and click OK

    Interesting :-k I would always do this from SSMS. Any reason for doing it from NAV?
    David Singleton
  • bbrownbbrown Member Posts: 3,268
    bbrown wrote:
    ...

    For SQL (Database - File - Alter). Just change the file size and click OK

    Interesting :-k I would always do this from SSMS. Any reason for doing it from NAV?

    Either way the result is the same. I tend to use SSMS a lot myself. However, many clients I deal with have little or no in-house IT capacity. When working with these customers to handle task like database expansion it can be easier to keep them in the NAV client (a familiar place) rather then exposing them to the complexity of SSMS.
    There are no bugs - only undocumented features.
Sign In or Register to comment.