Great Plains Posting Batches in any module takes forever

(0) Share

Report

Posted on by Prasan.naik

We have been having this issue for a few days. Batches being posted into Payables, Receivables or plain Journal Entries seem to be taking a long time (an hour or more) - even if the batch had less than 10 transactions.

The batches get stuck at printing or Posting. When looking at the vendor details for example, the transaction get posted into the Vendor file as a payment to be made. However the related transactions on the GL doesn't get posted (or takes too long)

On the dB server itself, I have seen the tempDB file being constantly used through out the day and it looks like its reading and writing at the same time 24x7. Doesn't matter how many users are on the Great Plains, the tempDB is constantly being used to almost the maximum IO capacity of the Hard Drive.

I have tried using batch recovery tools from Microsoft (Fixit - http://support.microsoft.com/kb/850289 ) and manually removing the posting batches from the tables (SY0800 SY00801 PJOURNAL).
Both the solutions sort of help. The batches do get posted to the GL and to the Vendor detail. However the batch remains in the batch list as available to post. I have also tried restarting the servers and none of that really helps.

Anyone have any ideas on this ?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Community Member on at

Like (0)

Report

RE: Great Plains Posting Batches in any module takes forever

Like you said in the Answer, just run the GP Menu > Maintenance > SQL Maintenance, correct? We have never run maintenance since the upgrade last year so that is probably where our sluggishness comes from. Thanks for the resources!

Was this reply helpful? Yes No
Prasan.naik on at

Like (1)

Report

RE: Great Plains Posting Batches in any module takes forever

Hi Starlinger,

I would run the Maintenance on individual category of modules. That way you can isolate where the issue is.

I would start with the financial as usually that would be the one with the most of amount of data entry and edit.

The procedure doesn't pinpoint or notify where the issue lies. It just recompiles all the tables, indexes and SP to better run for the data that's in the tables.

Check these out for additional resources:

www.mssqltips.com/.../performance-analysis-using-sql-server-2008-activity-monitor-tool

www.simple-talk.com/.../how-to-identify-slow-running-queries-with-sql-profiler

logicalread.solarwinds.com/recompiling-statements-sql-server-pd01

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Great Plains Posting Batches in any module takes forever

Hey I know that this was a while ago but how did you figure out which tables you had issues with? Thanks!

Was this reply helpful? Yes No
Verified answer

Prasan.naik on at

Like (0)

Report

RE: Great Plains Posting Batches in any module takes forever

Its funny I had this same issue a year later and I thought of this post. I didn't come back to this post earlier as I had managed to solve it a few days later. Sorry about that and thanks for the replies.

Basically, it involved recompiling the Stored Procedures + updating statistics on the financial tables and restarting the server. As per GP documentation this is something that needs to be done often enough. And we hadn't done it in over a year. We also had our year end and the creation of our new fiscal year just before this issue started. Both these are SQL functions actually can be automated and run by the server.
Found this issue by finding that one of the SP was stuck. Caught that using the sql analyzer /activity monitor

To run it manually, Open the SQL maintenance window on GP

GP Menu > Maintenance > SQL Maintenance.

Select the tables from the database having the issue. In my case it was the financial tables. I just picked them all in the Financial series and ran the "recompile" and "Update Statistics" options.

Anyways, thanks to the two of you trying to help out here !

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: Great Plains Posting Batches in any module takes forever

Parsan,

I normally see problems like this on virtual machines I use for testing and demonstration. It is typically caused by insufficient system resources - mainly RAM. You did not describe your hardware configuration. Here are a few things to look at.

First, if you're running SQL on a 32 bit machine, and only have access to 4GB of RAM, this situation needs to be addressed. When the server runs out of RAM it begins to treat hard drive space as RAM, which is going to run terribly slow by comparison, and would match the symptoms you describe.

Second, if your databases are set for FULL back up (you can verify this under, Properties and Options in SQL Server Management Studio), then log files will continue to generate, and never automatically truncate, which can fill up a hard drive with data of dubious value.

Third, certain versions of 2010 have a bug, which fills up the SY07110 table in the Dynamics database with junk data. You can run the following query against this table, to determine how many records are in it - if the number isn't under low 4 digits (i.e. 1200), you can delete these records to speed up GP - Make sure you have a BACKUP:

select * FROM DYNAMICS.dbo.SY07110 WHERE CmdDictID = 6499

Finally, your RAID and Machine configuration are important. Incorrect settings here can affect your performance. I will not presume to speak intelligently on this subject, but I would strongly suggest testing the configuration with diagnostic tools.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

RE: Great Plains Posting Batches in any module takes forever

My first suggestion would be to look for any errant triggers. Does any access the database through SSMS? Do you have any third party products that were recently installed? After hours I would go in to SQL when you are the only one in GP and run SQL Profiler.

Was this reply helpful? Yes No