Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
SQL Mdf File is increasing rapidly.
rchikka
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.
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.
0
Comments
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?