Alteryx Engine error: Allocating requested dedicated sort/join memory would be mor than ph

[Djaber]

Hello Everyone, 

I'm facing an Alteryx problem / Error, 

Context:

I use SQL Agent to run Alteryx workflow daily and it works very good excepting one workflow who's giving me back an error saying :

Alteryx Engine: Allocating requested dedicated sort/join memory would be more than physical memory

The problem is that i execute the workflow using run button of Alteryx and it works but not using SQL Agent

in attached files you will see my sample workflow

Can someone help me please? did you faced a similar problem ?

for information the database, SQL Agent and Alteryx are in the same machine and also used by the same user

Thanks in advance

[danilang]

Hi @Djaber 

This message appears when the amount of RAM that the Engine wants to use for caching purposes is greater than the amount that the OS says is available.  What the Engine does in that case is use some amount that is less than the value returned by the OS and runs the workflow anyway.  The amount of available RAM reported by the OS is dependent on all the other applications and processes that are running on the computer at the time that the Engine makes the request.

This message is informational only and doesn't stop the workflow from running.  It will just have less RAM to use for caching purposes.

Dan  

[CharlieS]

Hi @Djaber 

This may seem counter-intuitive, but I suggest lowering the default sort/join memory in your system/user settings. Options > Advanced Options > System Settings then "Next" your way to the Engine > General section. Try halfing that amount of MB (if the "allow users to override settings" box is checked, do the same in your user settings). 

The key word in that setting is "Default". The Alteryx engine will allocate memory as needed to complete the task at hand. Increasing the starting point may skip a step in this process and help the engine run a bit better, but more isn't always better. 

[Djaber]

@danilang It was an error that occurred when I started the workflow from the SQL Agent job, but when I run it from alteryx, no problem occurred, even warning messages = 0

The error message appears in SQL Agent  and the error also in sql Agent (who start Alteryx engine and run the workflow mentionned in the parameters)

Any one have an idea of how can i solve this ERROR ?!

[danilang]

Hi @Djaber 

When launched from SQL agent, does the Allocation requested... message result in an error that stops your workflow from running?

Dan

[Djaber]

@danilang , No i didn't see this message" Allocation requested..", execution stoped with the message : 'Alteryx Engine: L'allocation de la mémoire de tri/jointure dédiée demandée dépasserait la mémoire physique disponible.'

[danilang]

@Djaber 

I believe that "Alteryx Engine: L'allocation de la mémoire de tri/jointure dédiée demandée dépasserait la mémoire physique disponible." is the French version of the message that I was referring to.  

I'm not familiar with using SQL Agent to launch Alteryx jobs, but it must do something equivalent to running this

C:\Program Files\Alteryx\bin\AlteryxEngineCmd.exe" MyWorkflow.yxmd

Can you try the command that SQL Agent is running in a Command window and see if the workflow stops?  If it doesn't then maybe there is a configuration option that you can set in SQL Agent to ignore Errors/Messages

Dan

[Djaber]

Hi @danilang , Yes it's exactly the isntruction i used in sql agent and it works fine in the command line (but not within SQL Agent)

Do you know please which part in  sql agent should i configure to ignore this error ?

Thanks 

[danilang · Accepted solution]

Hi @Djaber 

Like I mentioned previously, I'm not familiar with SQL agent, but the MS SQL Agent online help may hold the answer.  Step 7 "In the Process exit code of a successful command box, enter a value from 0 to 999999".  I don't know for sure what AlteryxEngineCmd.exe returns as the result code for a successful run, but it's probably 0.  Check the result code when you run from the command line and enter this number.

Dan 

[Djaber]

Hi @danilang , 

that was the solution, thanks 

Best regards

Djaber