"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

I have the same question (0)

All responses (11)

Answers (4)

Verified answer

Redbeard 12,931 on at

Like (2)

Report

1. The job is recommended and required in any production environment.

2. The job periodically clears out the PJTEMP table, and keeps this file from growing out of control.

3. The PJOURNAL Table should not have 8.6 million records.

If you carefully review the KB article at this link, you will find there is a section on the PJOURNAL file affecting performance. Recommending deleting the records in this file.

support.microsoft.com/.../898982

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

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,

Was this reply helpful? Yes No
mmurray 120 on at

Like (0)

Report

What is the location of the script for GP 10?

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (0)

Report

Change the GP2013 folder to GP2010 in the navigation.

Was this reply helpful? Yes No
mmurray 120 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
mmurray 120 on at

Like (0)

Report
[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).

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (3)

Report

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.

Was this reply helpful? Yes No
mmurray 120 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Kristie McNulty 1,008 on at

Like (0)

Report

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.

Was this reply helpful? Yes No