Optimization Problem

vikram7_dabasvikram7_dabas Member Posts: 611
edited 2008-11-25 in SQL General
My clients Database increases too much Day By Day.Database is in SQL.Database size is 19 GB with in 8 months.If I will optimize that DB then wht will happen?What r advantages and Disadvantages of optimization?When I go in File->Database->Information there it shows me size 19 GB.Is it contains size of .mdf,.ndf and.ldf files?or separately?
Vikram Dabas
Navision Technical Consultant


  • navuser1navuser1 Member Posts: 1,329
    The optimization process performs two functions:

    • For each Dynamics NAV table, the SQL Server indexes, other than the primary key,
    are rebuilt to optimize their layout and usage.
    • For each SIFT structure, any entries that contain zero values in all numeric fields are
    removed. The removal of these redundant entries will free space and provide more
    efficient updating and summing of SIFT information.

    from w1w1isql
    Now or Never
  • garakgarak Member Posts: 3,263
    which file is the larges? The Log file?
    Read for example this post and search the forum for LOG File Or Transaction Log or Maintenance.

    Do you make it right, it works too!
  • krikikriki Member, Moderator Posts: 9,079
    [Topic moved from 'NAV/Navision' forum to 'SQL General' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • strykstryk Member Posts: 645

    First of all I'd like to encourage you to search this forum for "SQL Performance" - and maybe especially about SIFT and INdexes - and you'll get TONS of useful advices, hints and recommendations!

    To measure the growth precisely you should check out the SQL procedure sp_spaceused (see "Books Online" for details) or the NDST (NAV Database Sizing Tool, part of SQL Resource Kit) - which is actually also using the sp_spaceused ...

    Besides the technical aspects like SIFT and Indexes you should also look into the application/business logic itself: limit the number of "Dimensions", limit the number of "Analysis Views", use "Date Compression" etc. ...

    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
Sign In or Register to comment.