Database size increasing hugely

ravi_navisionravi_navision Member Posts: 102
edited 2008-12-22 in SQL Performance
Is any one experienced this problem

Client using Nav 4.0 SP3 & SQL Server 2005.

For every 1 to 2 months the database size increasing drastically (last month it reached to 30GB). When we do Navision restore from proprietary backup (not SQL backup), database size shrink to 3 or 4 GB and works fine. But when we do SQL restore from SQL backup, there is no change in the database size (i.e; same as previous size). Again after some days when we check the database size it shows as 6 or 7 GB. After months it reaches to 20+ GB. There is no complaint about the business process except Performance issue.

How can I trace this problem?


Thanks,
ravi
~~ravi

Comments

  • sacmonsacmon Member Posts: 29
    Please check recovery model in Database > Alter--if there is Full then click on Boolean field Shrink to true. It will shrink the database to its original size.
  • ravi_navisionravi_navision Member Posts: 102
    Recovery Model option is set to ‘Full’ and I put a check mark on boolean field “Auto Shrink”. Closed and reopen the Navision to check the size, there is no change; it is same as the previous (7GB)
    ~~ravi
  • sacmonsacmon Member Posts: 29
    Then also you have to check in SQL Server's recovery model it will also shows you Full, you have to change that recovery model Full to Simple.

    Mind it, before doing this activity you have to take up the backup then change the recovery model to Simple and then restore it, it will definitely solve your problem.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    I am really not too happy with the advise on this thread.

    You should NEVER use Autoshrink with Navision, NEVER.
    David Singleton
  • garakgarak Member Posts: 3,263
    =;

    Do you have checked why the DB file increase?
    Is it "only" the LOG File or realy the mdf / ndf file(s) ?
    I belive you have Recovery Model Full. and this is good so for a productive database.
    What for maintenance jobs do you have?
    And as David said. NERVER USE AUTOSHRINK.
    So find out, why the files are increase.
    Do you make it right, it works too!
  • idiotidiot Member Posts: 651
    Can anyone share why autoshrink should never be used with Nav?
    What are the consequences if used?
    NAV - Norton Anti Virus

    ERP Consultant (not just Navision) & Navision challenger
  • bbrownbbrown Member Posts: 3,268
    You should never use "AutoShrink" with any SQL database. If the database expanded there was probably good reason. If you autoshrink it (or manually) it is likely to just expand again. This constant expansion/shrink ends up fragmenting the database files on the drive and impact performance. Also you have no control over when, and in what sequence, SQL decides to run the AutoShrink process. Again a possible impact on performance.
    There are no bugs - only undocumented features.
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]


    1) Like David and Garak said : NEVER use autoshrink

    2) Like Garak asked : which file is growing : the DB or the log-file?
    2.a) If it is the DB, probably it is because the fill-factor of your DB is too low. It should be 90%. To fix this, just change the fill-factor to the correct value. You can shrink your DB, so the size is double the amount your data is taking (this way your DB doesn't need to grow for some time). The autogrowth should be used ONLY in case of emergency. You should (once a week or also once a month) check if there is enough space left in your DB. If not, you should manually grow your DB a fair amount, so you are again tranquil for some time.

    2.b) If it is the log-file, you should make TL-backups in the future or use another recovery model. To fix this, you should first make a full backup, then change the recovery model to simple. Shrink the logfile as much as possible (max 10KB!). Finished this, expand your logfile again to the size of your DB (this to eliminate internal fragmentation ; I hope to find some time these days to blog about this). Change recovery mode back to full and make regular TL backups (at least once an hour).

    In both cases, it check your disk if it has become fragmented (especially the DB- and log-files) and if they are, defrag your disk (of course, you need to stop the SQL-server service to do this).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • strykstryk Member Posts: 645
    If the "net" size is growing much, and if the size is dramatically reduced after a native backup/restore process, then you should also look into your SIFT structures, means reducing the number of "buckets" and peridically cleaning so called "zero SIFT records" ...
    (if you search the forum for "SIFT" you'll find plenty of advices about "how to ...")
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • garakgarak Member Posts: 3,263
    Thats right, but first he should say which files increase hugely.
    Do you make it right, it works too!
  • strykstryk Member Posts: 645
    garak wrote:
    Thats right, but first he should say which files increase hugely.
    Well, actually my guess is that we're talking about the Transaction Log here: the Recovery Model is FULL, and I suppose there are no TLog backups performed, hence the file is growing, growing, and growing ...

    But as we are discussing "database growth" we should consider anything, too 8)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • ara3nara3n Member Posts: 9,255
    stryk wrote:
    If the "net" size is growing much, and if the size is dramatically reduced after a native backup/restore process, then you should also look into your SIFT structures, means reducing the number of "buckets" and peridically cleaning so called "zero SIFT records" ...
    (if you search the forum for "SIFT" you'll find plenty of advices about "how to ...")

    You can also do an exe upgrade to 5.0 sp1 which gets rid of SIFT and replaces them with Indexed views.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSterDenSter Member Posts: 8,304
    @ Ravi:
    There are two important things you need to do:
    1 - Hire someone who knows about SQL Server and let them show you how it works.
    2 - Buy yourself a book about SQL Server as a reference

    @ sacmon:
    Same goes for you, get yourself a good SQL Server book and read about the things you are giving out advice for. It looks like you are misunderstanding some of the options.
  • garakgarak Member Posts: 3,263
    I'm also sure that it is the TransLog.

    But now it's time for Ravi_Navision to give a statement.

    @ Ravi / @ sacmon:
    A SQL Server book is a good XMas present for you and you can decant it from the fiscal (here in Germany it's possible because it's a book for the work)
    Do you make it right, it works too!
Sign In or Register to comment.