When MS Dynamics AX 2009 system maintenance went wrong
Things appeared fine initially, but as days progressed with increase in number of users connecting to the system across geographies the AX2009 system started showing signs of freezing. The users when attempted to do anything in AX, were faced with system freeze as the system did not allow them to place orders, do payment management, or other things associated with the functions they needed to perform on daily basis.
Within some time it became practically impossible for the users to work. A severity Level- 1 help desk call was logged by the customer IT service team and was assigned to the Infosys Application support team. Further the scheduled full backup failed post maintenance created another critical issue.
During the initial investigation it was observed that There were lot of Locks in the database as first action the AX support team tried to free locked sessions by seeing the blocking SPIDS and killing them to see if the problem goes off, further the team did AOS servers restart but the problem continued. On detailed analysis it was found there were lots of Page waits on the Inventory and sales related transactions. The individual waits were not that bad but there were lots of them, they seem to be on inventtrans regularly.
Half the indexes were missing on the inventtrans on Sql Server database also there was no clustered index and the table was a heap leading to trashed performance.
It Appeared that the Indexes on the Invent Trans table which was huge table of over 20 GB size have got accidently deleted along with few other tables while doing maintenance which was causing huge paging issues and freezing in the AX 2009 database. It appeared possibly Indexing process stopped abruptly while Index reorganisation job was executing .Further on investigating the cause of failure of the backup job it appeared that the trashed indexes on the database had an impact on the overall table sizes, increasing the database size and increasing the backup size tremendously leading this is a disc full during backup error.
Once the team had identified the issue as resolution we had to run AX 2009 Synchronization from Application side as to ensure the Indexes at application level matches with Indexes at Database level. For that we had to get AX2009 system offline. It took couple of hours to recreate all indexes as part of running synchronization to build all Indexes. On Inventtrans it took longest time. As all the Indexes have synchronised post running the AX Application Synchronization, after doing basic testing the AX2009 system was issued back to users under monitoring till the time users reported that all the operations are fine. It was also decided to revisit the backup strategy as client were left with only one Backup and the clean up script deletes the backup which is x hrs old since it is purely date/time based.
From Next blog I will continue my journey of our AX 2009 rollout in Eastern Europe. Signing off for now
Cheers
Pankaj
