Monday, October 19, 2015

Thousands of orphaned records in SY00800 and SY00801 cause batch posting to be very slow

By Steve Endow

I just finished a call with a Post Master Enterprise customer who was experiencing posting issues and errors in one of their Dynamics GP companies.

Batches would post fine in Company 1, but when Post Master tried to post a batch in Company 2, the batch would take 5 minutes to complete and conclude with a strange error, when it should take less than 20 seconds.

The customer's environment is moderately complex, with numerous complex system integrations running constantly, so we initially thought that maybe the integration was locking some tables.  But the same integration was importing data into both Company 1 and Company 2, so that didn't seem to explain why only one company was slow when posting.  We even tried turning off the integration, but batches still posted slowly.

I then ran a diagnostic database trace utility that is included in Post Master, and I happened to notice that there were a ton of records in the SY00800 table.  We queried the table and found that there were almost 2,500 orphaned records in the SY00800 related to Company 2.  We then checked SY00801, and similarly, there were 2,500 orphaned records in that table as well.

We deleted the Company 2 records from both SY00800 and SY00801 and performance instantly improved.  A few test batches posted in under 20 seconds.  No more errors.  Deleting those orphaned records appears to have resolved the issue.

After working with Post Master and learning about the Dynamics GP posting process for over 6 years now, I still run into very strange situations with client environments that leave me baffled.

For example, how is it possible to get 2,500 orphaned records in both SY00800 and SY00801?  The mind boggles.

Anyway, it's probably a good idea to occasionally check those tables and clear them out if you see a lot of orphaned records.

Happy hunting.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: