Index created in SQL and not in AOT

(0) Share

Report

Posted on by Vamsa

142

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

I have the same question (0)

All responses (6)

Answers (3)

Sort by

Verified answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Vamsa 142 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Vamsa 142 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report
Copy link

Link copied!

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".

Was this reply helpful? Yes No
Vamsa 142 on at

Like (0)

Report
Copy link

Link copied!

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

Error:

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

Was this reply helpful? Yes No