Database Size

lovelyk
lovelyk Member Posts: 83
edited 2009-06-11 in NAV/Navision Classic Client
Dear All,

my client's database size is around 450 GB which i want to reduce.

client is using NAV 4.0 sp3 with sql server 2005.

kindly advice

Comments

  • idiot
    idiot Member Posts: 651
    edited 2009-06-11
    Try the jobs under Administration -> IT Administration -> Data Deletion followed by database shrink if needed, consequences wise it's up to you to find out... [-X [-X [-X [-X
    NAV - Norton Anti Virus

    ERP Consultant (not just Navision) & Navision challenger
  • Belias
    Belias Member Posts: 2,998
    no, don't start with data deletion: maybe the problem is somewhere else: did you check how large is your transaction log?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • kriki
    kriki Member, Moderator Posts: 9,129
    Do you also have a correct fillfactor (+ 90%) and a good maintenance plan to rebuild the indexes?
    Do you have done some SQLtuning? A sideeffect of that is that the DB decreases in size.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Belias
    Belias Member Posts: 2,998
    and also upgrading to 5 sp will cut out sift tables (but you have to be very careful because of performance issues)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • lovelyk
    lovelyk Member Posts: 83
    Dear Belias,

    My database size is around 200 GB & Log is around 250 GB.

    could you kindly guide me If I truncate the log the i will not harm the processing of my operation.

    Regards
  • Belias
    Belias Member Posts: 2,998
    lovelyk wrote:
    Dear Belias,

    My database size is around 200 GB & Log is around 250 GB.

    could you kindly guide me If I truncate the log the i will not harm the processing of my operation.

    Regards
    there are a plenty of topics in mibuso written by sql experts (not me :mrgreen: ): search mibuso for "truncate log" or "full recovery model"...you should find the answer with explanation, too.
    edit: truncating and shrinking log, should reduce it TO (not "BY") some MB (you can choose how big you want it, but take a look to other topics in order to find the best solution for you)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • David_Singleton
    David_Singleton Member Posts: 5,479
    idiot wrote:
    Try the jobs under Administration -> IT Administration -> Data Deletion followed by database shrink if needed, consequences wise it's up to you to find out...
    Wow nice advise. =; NOT!!!!

    Although most of us will interpret this as sarcasm, you should have added a smiley or something, since lovelyk might take you seriously and do this.

    lovelyk, note that the data compression routines are very dangerous, and you need to do extensive testing before you even think of running them. If you do run them, it may be impossible to upgrade you system later.
    David Singleton
  • David_Singleton
    David_Singleton Member Posts: 5,479
    lovelyk wrote:
    My database size is around 200 GB & Log is around 250 GB.

    could you kindly guide me If I truncate the log the i will not harm the processing of my operation.

    lovelyk you seriously need to get some NAV/SQL expert to help you ASAP. What has your partner said about this? If your partner can not help you, then maybe try contacting Microsoft directly to see if they can help, Microsoft are doing a lot in India right now to help resolve issues like this with partners and customers.
    David Singleton
  • idiot
    idiot Member Posts: 651
    David Singleton wrote:
    ...
    Although most of us will interpret this as sarcasm, you should have added a smiley or something, since lovelyk might take you seriously and do this. ...

    Ok done boss.
    NAV - Norton Anti Virus

    ERP Consultant (not just Navision) & Navision challenger
  • stryk
    stryk Member Posts: 645
    Hi!

    With NAV 4.0 you should look into SIFT optimization first (see http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx)
    this would erase a lot of SIFT records, thus reducing the db size (usually remarkably).

    As you TLog is so big:
    Are you running TLog Backups? If not you should. Or change the Recovery Model to simple.
    Once the TLog is truncated you could shrink the physical file using a DBCC SHRINKFILE command (see Books Online about details)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool

Categories