Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K 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
- 298 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
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Delete Past Records
roshandilruk
Member Posts: 51
Hello Pals,
Our Nav database has another module called POS. That is what the main application which we use to do sales. There are two main tables, POS Header and POS Lines. They are more over similar to Sales Inv. Header and Sales Inv. Line tables. The tables are related with their Primary Keys.
The issue is, the database is getting much bigger. We find it hard to delete the old records from the POS Header and POS Line tables as it consumes such a long time. We have written number of programs to Delete past data, when we try to delete for a longer period (a month or more) , it takes a long time for the delete process and finally throw an error with no sufficient space. However, even if we expand the database by 10 GB, still the issue remain the same. This also slowdown the entire database.
Does anybody have an idea to write a Delete Program to flush the history data, but more efficiently while it consumes less time and resourses?
Thanks
Our Nav database has another module called POS. That is what the main application which we use to do sales. There are two main tables, POS Header and POS Lines. They are more over similar to Sales Inv. Header and Sales Inv. Line tables. The tables are related with their Primary Keys.
The issue is, the database is getting much bigger. We find it hard to delete the old records from the POS Header and POS Line tables as it consumes such a long time. We have written number of programs to Delete past data, when we try to delete for a longer period (a month or more) , it takes a long time for the delete process and finally throw an error with no sufficient space. However, even if we expand the database by 10 GB, still the issue remain the same. This also slowdown the entire database.
Does anybody have an idea to write a Delete Program to flush the history data, but more efficiently while it consumes less time and resourses?
Thanks
0
Answers
1. SQL or Native?
2. How large is the database and how much free space?
3. Why are you deleting these records in the first place?
At the moment the size is 90GB
We do not need to keep the Data except for the last couple of years, but the database has data for last 6-7 years.
Reason : The licence only permits to expand the databse up to 130GB. As its already 90GB, the data has to be deleted to save the space.
You can update the license to 256 GB (no cost).
You should consider moving to SQL. I know its a big job, but this issue is only going to get more and more complex, and the longer you leave it the more difficult it will be to swap.
In terms of data growth during delete, you could encapsulate a particular group of consistent records, and then use a commit between each delete.
Cheers!