SQL error when Adjust Cost runs.

spider1269spider1269 Member Posts: 67
edited 2023-01-17 in NAV Three Tier
We're getting the following error just after Adjust Cost runs. We have as NAS job running Adjust Cost and the job will fail. The error is very random. It hasn't happened for 3 months and then happened 3 times in 4 days.

The following SQL error was unexpected. Execution Timeout Expired.

According to our DBA this is a SQL out of memory error. We have 32GB on the server so we're not sure where to go from here.

Answers

  • mohana_cse06mohana_cse06 HyderabadMember Posts: 5,496
    Please check the SQL Command Timeout property on the Service.
    The default value is 30 mins so increase it and try again.

    https://learn.microsoft.com/en-us/previous-versions/dynamicsnav-2016/dd355055(v=nav.90)?redirectedfrom=MSDN
  • PascualPascual Member Posts: 9
    Check that you don't have an infinite recursive function or a loop that never ends, I think I remember that this happened to me and debugging the code we saw that this was the problem.
  • spider1269spider1269 Member Posts: 67
    Thanks for both suggestions. I'll bump up the SQL Command Timeout and see if that helps.

    Would an infinite recursive function exist within the Adjust Cost process? It's standard NAV code but it's being run through NAS.
  • bbrownbbrown MAMember Posts: 3,238
    A common cause of a recursive loop in Adjust Cost is a production order consuming its own output. This can also be several cost layers deep.

    There are no bugs - only undocumented features.
  • DenSterDenSter Flagstaff, AZMember Posts: 8,288
    edited 2023-01-20
    There's a safety against going too many levels deep in BOM levels (I think it used to be 50 levels)
    This is due to a table lock that takes longer to resolve than the timeout setting on SQL Server. You could increase the timeout, but better would be to actually solve performance issues. SQL Server setup, maintenance, and of course custom code
  • spider1269spider1269 Member Posts: 67
    We're not using BOM's and are strictly a distribution company. The error is fairly consistent though as it happens just after the Adjust Cost job kicks off. I'm going to look into the timeout issue to see if we can resolve it that way. The Adjust Cost is out of the box so any customizations (which we have plenty of) shouldn't be an issue.

    Thanks for all the suggestions. I've been working with NAV for 20 years and after we transition to the new ERP I plan on looking at retirement. It's been a fun run but will be glad to not have the 2 AM call that NAV is "running slow".
Sign In or Register to comment.