SQL Mdf File is increasing rapidly.

rchikkarchikka Member Posts: 23
Hi,

We are facing issue with SQL DB size which is increasing very rapidly ( of course the customer do have multiple stores /locations and day today transactions/documents are huge )... with in 5 years of inception, the DB size has grown more than 300 GB ( MDF file only ). we are taking care of the Log file by shrinking on regular basis.

Now the customer wants to reduce the DB size as it is having impact on performance also. we do have performed the below mentioned tasks already.

1.DB Optimization by going to File -> DB -> Information -> Optimization ( All the tables once and also individual tables at a time )
2. Specific tables mentioned below are taking lot of space : ( ex. )
GL Entry 27.8 GB
Item Ledger Entry 13.5 GB
Value Entry 39 GB
Ledger Entry Dimension 8.1 GB
Transaction Header 15.5 GB
Trans. Sales Entry 49.7 GB
Trans. Payment Entry 16.7 GB
Trans. Sales Entry Status 15 GB
Analysis View Entry 9 GB

3.Date Compression option has been tested. However this option deletes all the transactional entries before grouping the amounts on Each GL Account wise for the selected financial year. ( By doing the compression, we will be losing the detailed information from the ledger entries tables and thus No Ageing will be made available ( correct us if our understanding is wrong )

4. No BLOB fields were used for Item card


Kindly suggest if have missed any of the critical points to fine tune ?



Regards,
chikka.

Comments

  • bbrownbbrown Member Posts: 3,268
    The first thing is to stop shrinking the log file. That will do far more harm then good in terms of performance. Set the log large enough so it does not need to auto-expand between log backups. Oh yes, and setup log backups if they are not already running.

    Second is step away from the idea that the database size is the primary cause of the performance issues. With systems I work with, I'd consider 300 GB in 5 years about average.

    Then investigate the real sources of your performance bottlenecks and address. Search this forum. Lots of info.

    Could you also post some addtional information such as hardware information, SQL version, NAV version, etc?
    There are no bugs - only undocumented features.
Sign In or Register to comment.