Dynamics Community Forum Thread Details

Microsoft Dynamics NAV Database Optimization

Dear Experts,
We have some clarification / support / guidance on Microsoft Dynamics NAV Database Optimization,
1) Our Current Microsoft Dynamics NAV database size is around 250GB, due to this huge database size we are facing issue with Backup Management System / Performance. Even it is increasing every month 10 GB (approx.).
2) For reducing the database size shall we use “NAV Optimize option” (File-> Database ->Information -> Tables ->Optimize)? Is this the best solution? Is there any impact after in using this optimization activity?
3) For your information, we made a test database base and did this optimization activity and reduced the database size from 250GB to 180GB (around 70 GB reduced).
4) Expecting your valuable suggestions / advise / solution.

Thanks

HI,

"NAV Optimize option" : File-> Database ->Information -> Tables ->Optimize is a old good solution because it's like a "complete rebuild indexes solution"; actually is not needed (is only for NOT sql environments and old NAV releases).

MAINTENANCE

Use SQL Server Maintenance Plans

- Create a plan to degrafment indexes (ex. every night) > Microsoft Script

look at my blog "Microsoft Script"

blogs.dotnethell.it/.../Defragment-All-Indexes-In-A-Database-Microsoft-Script__16778.aspx

- After recalculate statistics

- Create a plan to rebuild the indexes at least once a week (if you have time also each night instead of degrament)

- Rebuilding the index removes fragmentation inside the database and at the same time calculates the statistics

- Do NOT use autoshrink in properties or with a maintenance plan

PERFORMANCE

- Crete more datafiles on flegroup

After create a Reorganize job to move table on different datafiles (will reduce locks & disk I/O)