Job step failure due to insufficient system memory

Products

• Information Centric Analytics

Issue/Introduction

One or more steps in either the RiskFabric Processing or RiskFabric Intraday Processing jobs fails and a message similar to the following is logged by the SQL Server Agent:

Error Message: There is insufficient system memory in resource pool 'default' to run this query. [SQLSTATE 42000] (Error 50000). The step failed.

Environment

Release : 6.x

Cause

This error is caused by insufficient resources on the server hosting SQL Server services for the tasks being executed at the time of failure. This is most commonly seen when one of the RiskFabric processing jobs is running concurrent with other SQL Server Agent jobs (e.g., database backup or maintenance tasks). This can also occur when performing a sufficiently large DIM search in the RiskFabric portal while processing is running.

Resolution

Ensure the allocation of system memory to the SQL Server service is 80% by following this procedure:

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric database
3. In Object Explorer, right-click the SQL Server host name and select Properties
   
   The Server Properties window will appear
   
   
4. Select the Memory page
5. Under Server memory options, set the Maximum server memory (in MB) to a value equal to .8 • n, where n is the total system RAM installed on the server hosting SQL Server services
6. Click the OK button to save the change and close the Server Properties window
7. Open a Command Prompt as an administrator
8. Execute the following commands:

   net stop MSSQLSERVER

   net start MSSQLSERVER

   NOTE: If you are hosting the RiskFabric database in a named instance, execute these commands instead:

   net stop MSSQL$<Instance Name>

   net start MSSQL$<Instance Name>

If SQL Server's memory allocation is already set to 80%, evaluate the following options:

1. Check for the presence of other job processes running concurrent with ICA's jobs and either disable or reschedule those jobs to run at a time when RiskFabric processing jobs are idle
2. Increase system RAM and increase the memory allocation to SQL Server to maintain an 80% ratio