"Remove Posted PJOURNALs" SQL Server Agent Job

(0) Share

Report

Posted on by mmurray

120

Hi folks,

I working as a DBA at my current company. I have three quick question for you all in regards to Microsoft Dynamics GP 10, specifically a SQL Server Agent Job.

Within our TEST environment we have a SQL job called “Remove Posted PJOURNALs From All Companies”. This job runs every 30 minutes and calls the smCleanupCompanyPostingJournalEntries stored procedure in the Dynamics database.

I would like to know if this Job is recommended/required in our PRODUCTION environment (currently it does not exist)?
What is this job responsible for? and how will it affect our day to day processing?
Should the PJOURNAL table in production have over 8.6 million records?

The reason I’m asking is that I’m seeing a stored procedure called glpGetAccountInfo execute and run extremely slow because it’s attempting to read through the 8.6 million records in the PJOURNAL table without a proper index. I thought the SQL Agent Job I mentioned above might have something to do with cleaning up this table, therefore speeding up this process.

Any help/information would be greatly appreciated.

Thank you!

*This post is locked for comments

All responses (11)

Answers (4)

Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

Kristie -

I would suggest checking the log file on the job to see if there is any insight there. I have found through long experience that a good percentage of problems are related to security. If the user associated with job doesn't have the appropriate permissions, it would not be able to complete the task. The user associated with the job can be viewed/set in the Properties of the Job, under the General tab. Additionally, there is a check box on the tab, which should be checked to enable the job. Finally, you can choose when and where to write Notifications about the job under the Notifications tab (also in properties).
Kristie McNulty 1,008 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

Harry - what if the job is running every 30 minutes but data remains in the table anyhow? I'm able to delete manually, that's not been a problem, but I am curious why if the job is running as designed. Thanks.
mmurray 120 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

Thank you, that clears everything up. Appreciate the help.
Verified answer

Redbeard 12,931 on at

Like (3)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

Michael,

Backups are just standard practice when dumping any data. It is highly unlikely, you're going to create problems by clearing this table based on my experience, documentation and the KB I referred you to.

I recommended (TRXDATE) Transaction date as a filter to remove data, as the stated purpose of the PJOURNAL Tables is collecting posting data during the posting activity. So, picking a cutoff date, in the past, ensures you would not impact anyone in the system who happens to be posting and therefore writing records to PJOURNAL Table.

As long as no one is posting, completely clearing the PJOURNAL table manually will not affect anything - EXCEPT SYSTEM PERFORMANCE. I would strongly suggest doing this kind of activity during off hours, when you have exclusive access to the system.

For future reference, the number of records in the PJOURNAL should not exceed the number of records in the GL10001 table, with the exception being, when using allocation accounts.

Hope this clears things up.
mmurray 120 on at

Like (0)

Report
RE: "Remove Posted PJOURNALs" SQL Server Agent Job

[quote user="Redbeard"]

I would do a couple of things...

1. make sure you have a backup of the table contents, set aside.

2. use a data restriction to truncate the file, with a simple where clause on TRXDATE to remove everything prior to a specific date.

[/quote]

Thanks Harry, in the KB it states the following:

"If rows are returned, we recommend that you clear the contents of the table by running the following statement against all the company databases.

DELETE PJOURNAL"

I have two questions for you:

Why would I want to use a predicate (TRXDATE) on the DELETE if the table should technically be empty?

Is there a certain time of the day after specific processing/tasks have been completed before I DELETE data from the PJOURNAL table? I don't want to remove data that hasn't been processed yet (if that's the case).
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

I would do a couple of things...

1. make sure you have a backup of the table contents, set aside.

2. use a data restriction to truncate the file, with a simple where clause on TRXDATE to remove everything prior to a specific date.
mmurray 120 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

The first part of the KB article you mentioned said to DELETE PJOURNAL. Can I truncate this table instead? I'm afraid that the DELETE on 8.6 million records will take a very long time to complete.
Verified answer

Redbeard 12,931 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

Change the GP2013 folder to GP2010 in the navigation.
mmurray 120 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

What is the location of the script for GP 10?
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: "Remove Posted PJOURNALs" SQL Server Agent Job

Harry's advice is comprehensive, although, I am adding my part as well.

This is a definitely recommended job, it truncates the PJournal table in each company. In case it is disabled, it will result with having the table growing massively as your posting grows.

Therefore, you will have to ensure that Remove Posted PJOURNALs From All Companies job does exist and is enabled under the Jobs folder.

To resolve your current issue, check the PJJOB.SQL script found in Dynamics GP Folder on the following default path Microsoft Dynamics\GP2013\SQL\Util

Let us know if you have any further inquiries,