RECID Maxed out

We have been struggling with this issue of the system sequences table is getting maxed out on the RECID for some tables. While the table that gets maxed out are random and without a pattern it mostly happens for table no 65482.

We also identified that this happens mostly when some user logs into the system. Which led to a conclusion that there is something at the user login stage that is causing the problem. We took some help from microsoft support desk and generated the trace file for the login steps.

 To generate the startup trace file follow the following steps : -

1. Login to the application server where the AOS is installed. 
2. Start the performance monitor  (start -> run -> perfmon ) and initiate the trace log
3. Go to User Defined and new 
4. Create the new trace using the AOSTRACE.XML template 
5. Log in with a client and stop it

1.  Each trace would be created and saved in a folder with an .etl extension
2. Copy the trace file created and open the same in Microsoft Dynamics AX Trace Parser. (can be found on the product CD).

When the trace file was analyzed it was found that the system was trying to run the initialization checklist on startup. This was evident from the fact that there were records in the following tables with running status.

1. Syssetuplog 
2. Syssetuppartitionlog  

These records were deleted using the following scripts : -

Delete from Syssetuplog

Where description like ‘Running’

Delete from Syssetuppartitionlog

Where description like ‘Running’

Once the above records were deleted we emptied all the compilation log and libraries and did a fresh compile of the application. 

The following table was truncated in the model database to get rid of any previous logs

• SYSXPPASSEMBLY

       The following folders were truncated in the AX server folders to delete the CIL libraries

•  C:\Program Files\Microsoft Dynamics AX\60\Server\XXX\bin\XppIL

It is assumed that that the initialized checklist got initiated when the second AOS was installed on our server. The process followed to compile and load an environment with 2 AOS servers was as follows

1. Stop All AOS
2. Start one AOS and wait until it is Running
3. Run AXBUILD on that AOS C:\Program Files\Microsoft Dynamics AX\60\Server\XXX\bin\AXBUILD.exe xppcompileall
4. When Finished with compilation, Restart this AOS
5. Log into a client and run a FULL CIL
6. When finished, stop the client, Stop the AOS
7. Start the second AOS and wait until it is up running
8. Start the First AOS
9. Log in users

We also noticed an event viewer log suggesting a missing stored procedure in AX.
Run AXUTIL SCHEMA 
Run AXUTIL OPTIMIZE to rebuild the model database


Another possible reason that was potentially considered for the max out was that maybe due to internal procedures using the SystemSequences Table it is possible the locks are being escalated and the table is getting locked out. When AX is trying to read the locked table it is possible generating a higher no to avoid a duplicate.

ALTER TABLE SYSTEMSEQUENCES

SET (LOCK_ESCALATION = DISABLE);