Manage SQL Server Memory
Overview
Microsoft SQL Server is known to consume all of the memory available on a machine, allowing little to none available for other processes. This article details the process in which you can adjust the maximum amount that SQL Server will be able to use, therefore allowing other tasks and processes to run as intended.
This article details the 'how-to' of the process. The value you decide to set the maximum amount to is environment specific, and therefore there will not be a specific recommendation within this article.
Process
Open Microsoft SQL Server Management Studio and connect to your local database instance.
-
Right-click the server name and select Properties from the bottom of the list.
-
Select Memory from the left-hand pane.
Once here, you will see options for both Minimum server memory (in MB) and Maximum server memory (in MB). You will probably notice that the default setting is set to 2.14 Petabytes, an allotment that is generally impossible for typical business environments.
Adjust this setting to a value that fits within your environment, and then click OK.
SQL Server will then automatically adjust the memory that it reserves without requiring a restart of the SQL Server service.