RSMUS.comDynamics GP has a very important SQL job that cleans up records in a table called PJOURNAL. Without this job, the contents of the table will grow and eventually slow down the posting process in GP. This job has been part of the install for GP since version 7.5, but a change was made with the release of GP 2013 that may have resulted in a duplicate of this job on your SQL Server. If that has happened, you need to remove one.
A feature released with Dynamics GP 2013 was the ability to name the system database, traditionally DYNAMICS, to something of your choosing. With that feature came an update to the script which creates this PJOURNAL cleanup job. That job is named Remove Posted PJOURNALs From All Companies, or rather it was before version 2013. With this Named System Database feature the script that creates the job during installation was modified to add the system database name to the end. This ensured a unique job was created in the event you are hosting multiple GP system databases on one SQL Server. However, this change had a side effect. It is possible to get a duplicate of this job if you upgrade from a version prior to GP 2013. The new version of the script detects whether it already exists, but it fails to recognize that the earlier version did not have a system database name in parentheses at the end.
Why is this a problem? With a duplicate of this job for the DYNAMICS database, you now have the exact same job running every 30 minutes on your system. The job calls a stored procedure in the DYNAMICS database which in turn runs a procedure in each company database and this will result in deadlocks. Where I first witnessed this was with a client whose General Ledger import was failing every time it happened to be run at the same time as these two jobs – which was frequently given the jobs run every half hour.
Fortunately, the solution is simple. First, verify that each copy of the job you have does operate on the DYNAMICS database. This likely is the case, but it is always to best to double check. Open the properties of each job, select Steps from the navigation pane, then open the one step it contains using either the Edit button or by double-clicking the step in the Job step list. If both of your jobs target the DYNAMICS database, then you need to remove the older one. Delete the job whose name does not end with (DYNAMICS).
Note: You can find the script that creates this job in your GP directory – for a default install of GP 2018 it will be at c:\Program Files (x86)\Microsoft Dynamics\GP 2018\SQL\Util\.
