reorganize index: page level locking disabled

Hi,

Since a few days we notice the maintenance plan on our production database fails on the index reorganize of a specific index, raising this message:

Executing the query "ALTER INDEX [I_10780CREATEDTRANSACTIONID] ON [dbo]..." failed with the following error: "The index "I_10780CREATEDTRANSACTIONID" (partition 1) on table "LEDGERTRANSSTATEMENTTMP" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

We get the same error raised on the [I_10780CURRENCYIDX] index of that same table.

And few weeks before, we noticed the same issue with a few indexes on the LEDGERTRIALBALANCETMP table.

Both tables have the TMP suffix, so they seem to be (physical) tables used to temporarily store data.
They were both empty at the time we performed investigated the issue.  

And all indexes causing the problem allowed duplicates.

Based on that we :

• dropped the indexes from the SQL database
• performed a synchronize on those specific tables from within the Ax client

This resulted in the indexes being re-created again and the maintenance plan not raising any more errors.

We did found out the 'use page locks when accessing the index' option on the affected indexes was unchecked. After Ax had re-created the indexes, that property was checked.

We didn't find out who, what, why or when this page lock option was changed on the index.

But the above does seem to do the trick.

Lucky for us this happened on empty tables and non-unique indexes.

Therefor we took the risk and fixed this in a live system.

If it were unique indexes, we probably would have postponed this action until the next planned intervention where the AOS servers would go down.

Or we could have fixed it using the alter index command in combination with 'allow_page_locks = on', or just checked the appropriate option on the index itself.

Nevertheless, I do tend to stick with the principle that Ax is handling the data model (including indexes) and therefore prefer Ax to handle the re-creation of the index.