Index maintenance / data archival ?

(0) Share

Report

Posted on by Community Member

I've recently inherited a Navision SQL Server database, and am in the process of cleaning things up. No index or statistics are being maintained, no integrity checks running, inconsistent backups, etc. One of the representatives says there may have been concerns some time in the past, or a specific reason why the indexes were not being managed. That's what this post is for. Are there any known concerns with Navision SQL Server database maintenance? The Dynamics NAV/SQL Server configuration recommendations suggests that indexes/stats can be managed with Navision database just as anywhere else. The only quandary that I see is that there are 5590 tables, where 100+ have data in excess of 30, 40, 50 million records, even more than 300 million records in a few cases. It's SQL Server 2012, I'm not certain, however, what the Nav version is. I welcome all input.

*This post is locked for comments

All responses (10)

Answers (0)

Suggested answer

aavio 895 on at

Like (0)

Report

RE: Index maintenance / data archival ?

apart from archiving nav data, another way to reduce database size is by removing unused indexes.

Need to identify indexes that are consuming big size and that are least or never used.
Community Member on at

Like (0)

Report

RE: Index maintenance / data archival ?

No, the change log table is not the top one, though it is close, and I have already analyzed it and suggested to the customer that it could be easily archived. As I said, there are concerns about the integrity of an archive, so I am trying to find reference. Any actual NAV paper or reference anywhere that says yes, you can archive the change log table. if it's not the change log table, these are the appropriate methods for archiving data... does this not exist?
Suggested answer

aavio 895 on at

Like (0)

Report

RE: Index maintenance / data archival ?

As a first step, try to identify the top size consuming tables.

In most of the scenarios, Change log entry table used to consume the top spot.

if that is same in your case, you can archive this table.

other than this table, Data inside nav is not be seen just as historical data. it is financial data of that company. So prior to any archiving process, you have consult with CFO, NAV partner, NAV functional consultants. All ledger, register table, document data are correlated.

There is functions available in nav for deleting data,

check in administration menu-->IT Administration --> Data Deletion.
Community Member on at

Like (0)

Report

RE: Index maintenance / data archival ?

Thank you, Robert. I have seen your script and while i do not use maintenance plans, I have been working for quite some time to do many very similar variations thereof. I don't use autoshrink, I do have multiple data files and filegroups, and I have split up the integrity check from the indexes/stats, and i have split the indexes into multiple jobs, different levels of fragmentation, and I have tried to balance the tasks on different days throughout the week. The tasks I've written work - independently and collectively - but the time required for completion is too great. The problem is simple. There are far too many years of old/stale data in the system to manage the full database optimally, or in a timely manner at all. The available time 'after hours' is very limited, every day of the week. Surely you know as well as i do, combining all historical data with current data is not scalable nor manageable at all. That is the problem. I must ask again, is there any reference available for NAV data archival? I am very comfortable writing an archival routine, but my customer is concerned about the complications or data dependencies. I will compile an archival that will manage the current data effectively, and purge the old data regularly. Again, does anyone have any reference available for NAV database archival?
Community Member on at

Like (0)

Report

RE: Index maintenance / data archival ?

The database is about 1300GB. As I said, I have just inherited this instance, and am actively working to implement the appropriate maintenance, but that isn't really what the question was about. Some told me they thought they were told once not to run the index maint on NAVISION, and I was just trying to confirm if in fact any problems existed that I am unaware of. I think the best question at this point is whether or not I can archive the NAVISION database. There are several years of data in this thing, a great deal of which surely is just idle/dead data. I can design an archival process easily, possibly retaining only the current business year or quarter on line at all times. Is there any reference available for NAV data archival?
ajhvdb on at

Like (0)

Report

RE: Index maintenance / data archival ?

Thx for this info.

How much time per month do you think a sql consultant should spend on this kind of big database?
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

RE: Index maintenance / data archival ?

hi,

look at this my old "but also actual.." post

community.dynamics.com/.../134632

SQL INDEXES MAINTENANCE PLAN

(based on NAV Tech Days

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)

Microsoft Best Practices for NAV Performances

Optimizing SQL Server Performance with Microsoft Dynamics NAV

msdn.microsoft.com/.../hh169233(v=nav.71).aspx
Suggested answer

aavio 895 on at

Like (0)

Report

RE: Index maintenance / data archival ?

how big is the database size?

some customers might don't have enough free server time to run these maintenances, as there are other integration and replications jobs running overnight time.

so the time to complete these maintenance job need to be monitored.

since these maintenance jobs are not running for very long, it will take more time to finish in your first schedule. I would recommend to do a trial run index/statistics maintenance jobs in test server.

if its too long to complete index maintenance job, you can try to split the index maintenance job to run in batches like those fragmented >90%, fragmented > 75%

if the database size it too big, configure integrity job schedule to run in test server on the latest live backup(weekly).
Community Member on at

Like (0)

Report

RE: Index maintenance / data archival ?

Yes, I saw that one. I'm not really questioning the need to run the maintenance. Based on what one rep from the firm said, I was just asking whether there were any known concerns/issues that I should be aware of, or if there are any suggestions on the massive number of tables with such high record counts. Is this a norm, per se, for Navision?
mmv 11,467 on at

Like (0)

Report

RE: Index maintenance / data archival ?

Hi,

Have you checked the below post?

https://community.dynamics.com/nav/f/34/t/134632