A quick one but hopefully it will help someone else in the future.
We had an issue raised last week with a client running the “Select Cheques” routine in payables and although it appeared to be running normally it was taking much longer than usual and never seemingly completing.
As usual my first port of call is to run a SQL Profile Trace of the process (should probably be using extended events by now) to monitor exactly what’s happening.
When I setup an SQL trace I generally start with RPC:Completed and SQL:BatchCompleted as per below:
After running this trace I could see that the process seems to be showing a loop where’s its SELECTing data and them attempting to INSERT into the DEX_LOCK table over and over again for the same document:
To investigate further I stopped the trace and added “Errors and Warning” and restarted the trace again
The trace now shows the issue. The system is producing a duplicate key error when trying to INSERT into the DEX_LOCK table.
Because of this it seems to loop back and then try the INSERT again. The error isn’t being reported to the end user so they just think the process is running but taking longer than usual.
As expected when i query the DEX_LOCK table the row_id (42468782 – yes this client posts LOTS and LOTS of payables data) relates to the DEX_ROW_ID in the PM20000 for the document mentioned in the SELECT in the trace:
To fix the issue I deleted the offending row in the DEX_LOCK table as per below and the “Select Cheques” process continued as normal. (I didn’t even have to stop the “Select Cheques” process, I just deleted the row and it went through fine)
I hope this helps anyone else facing a similar issue.
Thanks for reading!
The Dynamics Explorer 