Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Index created in SQL a...
Finance | Project Operations, Human Resources, ...
Answered

Index created in SQL and not in AOT

(0) ShareShare
ReportReport
Posted on by Vamsa 140

Hi 

We had an issue with DBCC consistency error after re-indexing it from AOT in AX.  Then we rebuild the same index in SQL and still error exists.

Later we dropped and created the same index from SQL, consistency error in SQL got cleared.  I have performed these tasks in test server.

Before proceeding in the production server, I would like to know whether the same process can be followed in SQL? Will future synchronization in AOT , remove the indexes which is re-created in SQL? Kindly let me know if my question is not clear.

Thanks

Vamsa

  • Vamsa Profile Picture
    Vamsa 140 on at
    RE: Index created in SQL and not in AOT

    Reindex is done  from AX  systems administrator - periodic - database-sql administrator in AX

    Error:

    DBCC error:index in I-3119GeneralJournalEntryIDx for table GeneralJournalAccountEntry

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Index created in SQL and not in AOT

    What do you mean with "re-indexing from AX"? And what was the "consistency error"?

    If you create indexes in SQL, don't use similar names than what AX creates. That could cause additional problems.

    Instead name them with something like "MyIndex".

  • Vamsa Profile Picture
    Vamsa 140 on at
    RE: Index created in SQL and not in AOT

    For better understanding:

    We noticed consistency error index in I-3119GeneralJournalEntryIDx for table GeneralJournalAccountEntry after re-indexing it from AX .

    In test server from SQL ,we rebuild the index, still error exists

    But when we drop and create ,consistency errors cleared

    Can we follow the same in Live server? Will future synchronization remove the indexes because it is created in SQL not in AOT?

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Index created in SQL and not in AOT

    What error did you see in dbcc?

    You should not "drop and re-create the same index in SQL". Instead drop it in SQL and synchronize it from AOT (you can right click the table and select Synchronize).

    Nothing that you do in SQL will impact AOT, synchronization works only from AOT to SQL.

    In general you should not do changes in the SQL schema, since AOT is the master for that. But, as mentioned, you can create temporary indexes directly in SQL in emergency situations.

  • Vamsa Profile Picture
    Vamsa 140 on at
    RE: Index created in SQL and not in AOT

    Hi Nikolaos. Thanks for the response . Index already exist in AOT. But we got error with the same index when we executed the command dbcc in SQL. Then we drop and re-created the same index in SQL . But we havnt disturbed index in AOT . Will recreate of index from SQL ,will affect AOT index for the same table? Will the recreated index will be deleted in SQL ? But in AOT we did not do any changes

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Index created in SQL and not in AOT

    DB sync will drop all such indexes from SQL which are not defined in AOT.

    You can create temporary "hotfix" indexes in SQL but you have to make sure that similar index is implemented in AOT before you do db sync for the next time.

    Other than that, I'm not fully if I understand your question and what you actually did.

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,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 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