CostSheetCache Table Size Too Large

(0) Share

Report

Posted on by bankk

6,609

Hi,

I was running the top table size report in SMS and was shocked to find that the CostSheetCache table was 31GB with 370K records. Unsure if this translate correctly but I was curious if there are available clean-up jobs available to recover some of the disk space. The larger question being whether I should be cleaning up this table (why / why not?). Thank you!

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (1)

Suggested answer

raelhefn on at

Like (0)

Report

Try Table indexes, should be rebuilt periodically to ensure optimum data query performance and synchronize database . Re-indexing via Administration>Periodic>SQL administration + check database

Was this reply helpful? Yes No
bankk 6,609 on at

Like (0)

Report

Thanks for the reply Ramy. Actually before jumping to conclusions, I probably should have asked if the size is normal and is to be expected.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,119 Super User 2025 Season 2 on at

Like (0)

Report

Hi KBahn,

The business logic in AX seems to create new records in this table only. There is no cleanup task. I think the best you can do is create a support incident at Microsoft and ask them about this. The number of records and table size is too large in my opinion, but I'm not completely sure.

You can also try to delete it in a test environment to see if it impacts e.g. BOM cost calculations.

Was this reply helpful? Yes No
bankk 6,609 on at

Like (0)

Report

Thank you Andre.

If anyone's willing to share, I'm curious about the size of their COSTSHEETCACHE table. In production we're looking at ~475K rows and ~37GB. The values I provided in the opening post were from a test environment. TIA!

Was this reply helpful? Yes No
Verified answer

dekaasboer 1,530 on at

Like (2)

Report

Kbanh, if you go to production control -> setup -> routes -> cost groups.

Simply select a record and save it. AX wil delete all records by calling this method. (per company)

msdn.microsoft.com/.../costsheetfactory.clearcacheddata.aspx

Was this reply helpful? Yes No
bankk 6,609 on at

Like (0)

Report

Hello Rik,

I've tried your suggestion in a test environment with a COSTSHEETCACHE table size of about 30GB. The Cost group form is non-responsive so I left it running for about 3 hours with no identifiable change in the record count on COSTSHEETCACHE. 30GB of data is a lot to clear but it just appears to be stuck. Any ideas?

Thanks.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,119 Super User 2025 Season 2 on at

Like (0)

Report
Hi KBanh,

Rik deserves a Rookie of the week award for his answer. :) At least you can verify his answer.

The next code is used to delete the cache:

public void clearCachedData() { CostSheetCostGroupImpact costSheetCostGroupImpact; CostSheetCache costSheetCache; delete_from costSheetCostGroupImpact where costSheetCostGroupImpact.SheetId == sheetId; delete_from costSheetCache where costSheetCache.SheetId == sheetId; }

There is one single statement to delete the cache records. This is why you don't see the progress until it is finished. The number of records is very large in your environments.

You can check whether the cache table contains records for only one sheet ID or more. If it is one, you can eventually you can create a job like this to delete the records then. Probably this will be faster:

static void DeleteCache(Args _args) { deleteTable('CostSheetCostGroupImpact');
deleteTable('CostSheetCache'); }

Was this reply helpful? Yes No
bankk 6,609 on at

Like (0)

Report

I appreciate the follow-up Andre. I need to look at this a little closer.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

We have a batch job that we run periodically to purge the CostSheetCache table in all companies. It calls the CostSheetFactory.clearCachedData method directly. Yes, it takes a while. The other way to do this manually is to save the cost sheet itself.

Was this reply helpful? Yes No
bankk 6,609 on at

Like (0)

Report

Brandon,

Do you know approximately how large your COSTSHEETCACHE table gets before your purge job runs? And how long does it typically take to complete per company?

Thanks in advance.

Was this reply helpful? Yes No