This was only happening in one company, I could not recreate the
problem in the dev or test environments. I tried viewing the following logs,
which were no help: Windows Event Log, AX logs, SQL Logs.
It seems as if the table SysUtilElementsLog is
some kind of table locking/latching mechanism that was added in AX 2009
(possibly for helping manage resources shared by load-balanced servers or
something). This table contains a list of the tables (presumably, ones that
would have a lot of contention and you might want to manage via some locking
scheme). The most popular tables on the list contained the highest numbers. I
tried truncating the values (stop AOS, backup the db, set "UseCount
=1" for all rows, start AOS). That seemed to help for me, but not the
other users. #fail.
Then, I thought, maybe it
was a permission issue. (try….) Nope. #fail.
I did notice that, every
time I restarted the AOS, I could get in and do stuff, but it always started by
giving me a message (ONCE) about Cannot
create a record in Number sequence list (NumberSequenceList)… I went into Basic, Number Sequence, and did a cleanup
of the sequence in question and the error went away. It seemed odd that it kept
coming up every time I restarted the AOS. It seemed like that sequence
wasn’t incrementing or it got incremented but not saved to the database.
I did some more looking
around in AX until I found the following tables:
- NumberSequenceList -
the settings for all number sequences
- NumberSequenceTable - the state & locking table for number sequences
- NumberSequenceTTS -
the transaction table for number sequence locks
If you query SQL server
with the following query:
SELECT * FROM
NumberSequenceList WHERE DataAreaID = ‘[company with the locking problem]‘ AND
TransID > 0
This will show you the
tables with open/active transactions. Any one of these may be locking things
up.
You really want to keep
an eye on records with a [Status] = 1. This means that the record is in use and
may be blocking someone else from using it.
Compare the results of
that query to this one:
SELECT * FROM
NumberSequenceTTS WHERE DataAreaID = ‘[company with the locking problem]‘
EVERY record in this
table represents an open transaction and may result in a lock that blocks other
users or freezes up the AX Client for people.
It is particularly bad if
there is a row in the NumberSequenceList that does not have a matching record in the NumberSequenceTTS table
(matched on TransID). This would mean there is a lock in one table and no
reference by which to remove that lock. Basically an orphaned lock. Pure evil!
It just happened that my
orphaned record was on the NumberSequenceList but not the NumberSequenceTTS
table.
The way I resolved the
problem and cleared the log jam was to :
1. Stop the AOS
2. Backup the SQL
database
3. Removed the orphaned
record from the NumberSequenceList Table
4. Start the AOS
All of the other records
that were in the NumberSequenceTTS table were, apparently, waiting on the
orphaned record to clear, because they all got processed and cleared out.
The system was working,
no lockups or freeze ups.
Please, the action status its " Freed "
