Release CA Batch running forever and using up all SQL Resources

(0) Share

Report

Posted on by Community Member Microsoft Employee

I have a user that is creating batches in the Cash Account Transfer screen using a source to destination that have never been used before in this order. When she attempts to release the batch, SQL resources spike and all other users are either frozen or getting errors when trying to process their items in other modules. Got a copy of the database and running through the process on separate servers, been nearly 2 hours at this point, and seeing the same results. When look at the process through SQL Trace it almost appears to be going through the CA Integrity Check process of recalculating balances for all accounts and looks like it is trying to run to the end of time (seeing this in the Update cashavgd commands "pernbr= '610702'" with each new command being a higher number. How can we limit the max pernbr or otherwise correct this? She has been using this for a couple of months now and hasn't reported this issue before.

Thoughts any?

*This post is locked for comments

All responses (3)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Release CA Batch running forever and using up all SQL Resources

Thank you very much!!! Had a feeling it was a future date. Removing the extra 124,000+ records and going to run some test to confirm this works.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Release CA Batch running forever and using up all SQL Resources

This is almost always due to a very future period to post which causes a huge number of records in the CashAvgd table. Delete future period records from this table.

When you have a current backup:

Delete from cashavgd where pernbr > '201612' or something similar. Format is yyyymm.

Then look for the batch that caused this.

Select * from batch where status = 'U' and perpost >'201504'
Apps Mexico 1,090 on at

Like (0)

Report

RE: Release CA Batch running forever and using up all SQL Resources

You can open the SQL Profiler and configure a template like this. On Column Filters add an ApplicationName filter with the Cash Account Transfer screen example (%CA01000%).

After press the Run button, release the batch and you can check which query takes to long. Depends of that, you can take some actions.