[KBA-01223] SQL Server process uses all remaining server memory - Security Center 5.2 - 5.7

series
Security Center 5.2 - 5.7
revised_modified
2013-10-15

[KBA-01223] SQL Server process uses all remaining server memory

This article explains why the memory usage of the SQL Server process might increase over time until it uses all available server memory.

Applies to: Security Center 5.2 - 5.7

Symptoms

The memory usage of the SQL Server process increases until it has used all available memory on the server. This issue happens slowly over time or more rapidly when running a large report in Security Center. As a result, the system can slow down or roles no longer respond.

Cause

SQL Server uses memory dynamically when no maximum memory value is set. With this configuration, SQL Server queries the system periodically to determine the amount of free memory it has available. If there is free memory on the server, the SQL Server process might use it.

Workaround

You can set a maximum server memory value and restrict the amount of memory that SQL Server can use. Before setting the value, determine the appropriate memory settings by subtracting the memory required for the OS and any other Security Center roles or plugins installed on the server from the total physical memory. The difference is the maximum amount of memory you can assign to SQL Server.

To set the value for maximum server memory:
  1. Open SQL Studio Management.
  2. In Object Explorer, right-click the server and select Properties.
  3. Click the Memory page.
  4. In the Server Memory Options section, enter the amount that you want for the maximum server memory.