ODB-240000 ODBC errorr: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is insufficient system memory in resource pool 'default' to run this query. SQLState: 37000

[Shruti1]

Hi All

we are facing this error in wkf very frequnetly:
09/19/2023 6:05:04 AM enrich223 ODB-240000 ODBC error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is insufficient system memory in resource pool 'default' to run this query. SQLState: 37000

if anyone faced same issue
please help to undertand the rootcause and fix of this issue further

[LakshmiPravallika]

Hi @Shruti1 ,

Could you Please check the Target Schema used in your Workflow is occupying how much space in your Database memory and if it occupies more DB Space, it would be better to upscale your DB Size or Optimize the Data in this Schema so that this error dont occur again and also Please try optimizing the queries used in the workflow by giving the where conditions whatever only required and try debugging the Workflow.

Regards,

Pravallika.

[AkshayAnand]

Hi @Shrutii 

You can refer the link which might explain your query. It looks like your query result is fetching more rows than the allocated RAM space, Could you please relook the SQL query code.

Regards

Akshay

[Parvesh_Parmar · Accepted solution]

Hello @Shruti1,

The error message ODB-240000 ODBC error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is insufficient system memory in resource pool 'default' to run this query. SQLState: 37000 indicates that the SQL Server server does not have enough memory to run the query that Adobe Campaign is trying to execute.

First of all I would suggest you check which table consuming maximum memory.

 It can possible that some temporary table created by workflow not purged.

You can check for the keep in interim result flag in workflows property should be not checked. 

There are a few other things you can try to resolve this issue:

1. Increase the amount of memory allocated to the SQL Server server. You can do this by opening the SQL Server Configuration Manager and editing the properties of the SQL Server service.
2. Optimize the query that Adobe Campaign is trying to execute. This may involve breaking the query into smaller queries, or using indexes to improve the performance of the query.
3. Reduce the number of concurrent users that are accessing the SQL Server server. This may involve scheduling batch jobs to run at off-peak times, or using a load balancer to distribute the load across multiple SQL Server servers.

If you are facing this error frequently, it is important to identify the root cause of the problem. You can use the SQL Server Performance Monitor to track the memory usage of the SQL Server server and identify any queries that are using a large amount of memory.

Here are some additional tips for troubleshooting this issue:

• Check the SQL Server error logs for more information about the error.
• Make sure that the SQL Server server has the latest updates installed.
• If you are using a virtual machine for the SQL Server server, make sure that the virtual machine has been allocated enough memory.

In the specific case you can try to Restart the Adobe Campaign server. But is not solve the problem for long term.

Hope it will help you.

Kr,

Parvesh