Adjust Cost Item Entries batch job (795) is taking long time

Chandra_Bohara

Adjust Cost Item Entries batch job (795) is taking more than weekend hours (38 hours) to execute and we cannot run this batch during working days due to table lock error?:

Server Configuration

Windows Server 2008 R2 Standard, Service Pack-1

Processor:- Intel(R) Xeon(R) CPU X5650 @ 2.67 GHz 2.67 GHz

Installed Memory(RAM) :- 24 GB

System Type:- 64-bit Operating System

.....................................................................................

In both in inventory setup and accounting period, Average Cost Calc. Type was Item previously. We changed to Item & Location & Variant.

Additional information

Average Cost Period: Day

Automatic Cost Posting: Yes

Automatic Cost Adjustment: Never

Data of almost Three fiscal years (07/15/2012 to 10/04/2015)

1. Opening data as on 07/15/2012

2. First: 07/16/12 to 07/15/13

3. Second: 07/16/13 to 07/16/14

4. Third: 07/17/14 to 07/16/15

Fiscal Year & Inventory Period Closed till 07/16/2014

………………………………………………………………………………………………………………………………………………………………

Now system has to adjust cost for total of

183,922 item records

2,173,866 Avg. Cost Adjmt. Entry Point records

8,867,151 value entry records

………………………………………………………………………………………………………………………………………………………………

Out of above total, we selected inventory posting group “LUBE” which comprises

359 item records

181,530 Avg. Cost Adjmt. Entry Point records

1,259,046 value entry records

We started running the report on 10-apr-2015 1 PM and it was running till 11-apr-2015 4 PM and suddenly it got rolled back. So, it run for around 27 hours

It almost took our week end but did not complete.

However when we did try for 10 items, it completed in approximately 5 hours.

Due to “table lock” error, we are able to run this batch during working days (Sun-Friday). That means we can run only from Friday 6 P.M. to Sunday 8 A.M. (approximately 38 hours).

………………………………………………………………………………………………………………………………………………………………

All of the above, this batch job is taking long time to execute. Besides following workarounds

(1) Running adjust cost item entries for item in small buckets (let’s say 100 instead of 359 at a time)

(2) Running Adjust-Cost Item Entries first (without cost post to GL) and secondly running Post Inventory Cost to G/L (1002).

Is there any other ways to increase performance of these batch jobs?

lakshanvindana

Hi Chandra Bohara,

If you write to Microsoft about this, you can get a Hotfix from them as a solution.
We did that and got the solution from them, and it is working.

:thumbsup:

Chandra_Bohara

lakshanvindana wrote:

Hi Chandra Bohara,

If you write to Microsoft about this, you can get a Hotfix from them as a solution.
We did that and got the solution from them, and it is working.

:thumbsup:

Meanwhile can you share that hotfix with us please.

bbrown

How long has it been since you ran adjust cost? Are those number you present only the entries that need to be processed for adjustment? Or are they all the entries in the database?

Chandra_Bohara

bbrown wrote:

How long has it been since you ran adjust cost? Are those number you present only the entries that need to be processed for adjustment? Or are they all the entries in the database?

we are trying to revalue stock value item & variant & Location wise right from the beginning? Means those entries are all to be adjusted.

Jatin_Patel

Hi,

Open database information and check Database Used (KB) percentage.
Open Table Information by clicking Tables and run Optimize and then try Adjust Cost Item Entries.
It will increase performance of database.

bbrown

Chandra Bohara wrote:

bbrown wrote:

How long has it been since you ran adjust cost? Are those number you present only the entries that need to be processed for adjustment? Or are they all the entries in the database?

we are trying to revalue stock value item & variant & Location wise right from the beginning? Means those entries are all to be adjusted.

I see. Have you given any thought to restarting inventory? I'm working on a somewhat similar situation. The customer uncovered costing issues dating back 12 to 16 months. While "fix and run Adjust Cost" approach would fix things, it took over 4 solid weeks to process in a test copy. They cannot stop their business for that long. So, after much discussion, the decision was made to restart inventory. Not a small task of itself.

Chandra_Bohara

bbrown wrote:

Chandra Bohara wrote:

bbrown wrote:

How long has it been since you ran adjust cost? Are those number you present only the entries that need to be processed for adjustment? Or are they all the entries in the database?

we are trying to revalue stock value item & variant & Location wise right from the beginning? Means those entries are all to be adjusted.

I see. Have you given any thought to restarting inventory? I'm working on a somewhat similar situation. The customer uncovered costing issues dating back 12 to 16 months. While "fix and run Adjust Cost" approach would fix things, it took over 4 solid weeks to process in a test copy. They cannot stop their business for that long. So, after much discussion, the decision was made to restart inventory. Not a small task of itself.

what do you mean by "restart inventory"? can you clarify me further please. Does it mean like migrating opening data?

Chandra_Bohara

Jatin Patel wrote:

Hi,

Open database information and check Database Used (KB) percentage.
Open Table Information by clicking Tables and run Optimize and then try Adjust Cost Item Entries.
It will increase performance of database.

Jatin,

We have used SQL Server agent to automate the "update indexing & its statistic" at regular interval of a week. Is it still necessary to run optimization?

bbrown

what do you mean by "restart inventory"? can you clarify me further please. Does it mean like migrating opening data?

Basically yes. Clear out the existing inventory data, do a physical to establish beginning counts, establish unit cost, and load the beginning inventory.

ydiaa

can you share the solution if you already solve it , I have the same issue

manthonylugo

Jatin_Patel wrote: »

Hi,

Open database information and check Database Used (KB) percentage.
Open Table Information by clicking Tables and run Optimize and then try Adjust Cost Item Entries.
It will increase performance of database.

Anyone know how to do this in the newer version of NAV 2015 onwards?

Thank you.