SQL error when Adjust Cost runs.

spider1269

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.

mohana_cse06

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

Pascual

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.

spider1269

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.

bbrown

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.

DenSter

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

spider1269

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".