Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Why is AX Dropping Ind...
Finance | Project Operations, Human Resources, ...
Suggested answer

Why is AX Dropping Indexes?

(0) ShareShare
ReportReport
Posted on by Simon D 10

Hi,

I'm a SQL DBA with limited knowledge of AX.

I've added several indexes to an AX DB over the past couple of years and a handful of those are regularly deleted by AX (or at least the SQL account used by AX).  It's a small proportion of the indexes that I've created and it seems that it is something specific about those indexes, as within a day or so of re-adding them, they'll get dropped again.

This is not following a DB synch, it seems to be at random times of the day, while AX is being used.

Has anyone seen this before or have any ideas as to why it's happening?

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,253 Super User 2024 Season 2 on at
    RE: Why is AX Dropping Indexes?

    Hi Simon,

    There might be two reasons here:

    - Or the indexes which are not deleted are already added

    - Or it is on a table which is not part of the synchronization. Can you tell on which tables the indexes are not removed?

  • Simon D Profile Picture
    Simon D 10 on at
    RE: Why is AX Dropping Indexes?

    Thanks everyone for your suggestions.  It looks like I'm not going to find out why these specific indexes (and not the others) are being dropped.  I'm sure nobody is running a full synch or synching the affected tables.

    Within a few weeks the indexes will be added to the AOT and a full synch will occur, but this is outside of my control.  Hopefully once this happens, the indexes will stopped being dropped and I'll confirm it here once this happens.

    Thanks again for your time.

    Simon

  • Suggested answer
    Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Why is AX Dropping Indexes?

    I agree with Mäenpää's last reply.

    "Outside of an AX update, it's not doing a full synch" Regarding this statement you need to understand that system will through Index error in single table sync also. full sync is not necessary. 

    To check this add the two indexes (you mentioning to SQL table). Go to Ax AOT and try to sync the table by right-clicking the table and click synchronize. it will through index error.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Why is AX Dropping Indexes?

    Ok the account that you talk about is the AX / AOS service account. So you don't see which user actually did it.

    Anyway adding the indexes directly in SQL is not a long term solution and not supported, so I would use it just as a temporary fix until you can add the indexes in AX/AOT.

    Most likely you could investigate your problem further by talking to the users, and people who administer AX. We have zero visibility on that area. The explanation is in AX and in people who use it and do something there. The system will not sync anything to the database by itself.

    But as soon as you add the indexes properly in AOT your issue is gone.

  • Simon D Profile Picture
    Simon D 10 on at
    RE: Why is AX Dropping Indexes?

    Outside of an AX update, it's not doing a full synch, it's just selected indexes.  In this case I added 7 indexes, but only the same 2 keep being dropped.

  • Simon D Profile Picture
    Simon D 10 on at
    RE: Why is AX Dropping Indexes?

    The login that's shown as dropping the index is the same account that all the AX users use.  As I said, I have a limited knowledge of AX, so I don't know if that's how we've configured it or if that's how AX always works.

    You may be right about adding it to the AOT, that will be done in the next few weeks for these indexes.  But this is an ongoing problem it's been happening over the last 2 years, so it would be nice to understand why.

  • Suggested answer
    Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Why is AX Dropping Indexes?

    "We only synch the DB during an AX update". you mean to say you that you do full DB sync during an AX update. but AX keeps syncing Db during Table or EDT changes etc.

    I did not saw auto-drop of an index before. It normally threw an error during sync and developer have to drop before running sync again. (not full db sync though just sync the table).

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Why is AX Dropping Indexes?

    Ok, that seems strange. Did you discuss it with the user that you see in your logs? Perhaps they can provide some information about what they were doing.

    Having said that, I would not invest much time in investigating it. Instead I recommend adding the indexes in AOT and monitoring the situation. I'm quite sure that you won't have such issues once you've added the indexes properly in AOT.

  • Simon D Profile Picture
    Simon D 10 on at
    RE: Why is AX Dropping Indexes?

    I added the 7 indexes directly into SQL, not via AOT.  2 of them keep being dropped.  That's my normal procedure, I leave them in place for a while and when I'm happy that they're being used, I pass them to the DEV Team to update the AOT.  If they've not sufficiently used, I drop them myself.  I have now passed all 7 to the Dev Team.

    However, we only synch the DB during an AX update, which happens every few weeks.  The last update was over 2 weeks ago and I've created these indexes 3 times since.  They've been dropped soon after on each occasion.

    I've just checked with the Dev Team and they've confirmed that a synch has not been done since that last update.

  • nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Why is AX Dropping Indexes?

    Also, how can you be sure that nobody synced the database (or some tables) from AX?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans