How to update the Max Memory at SQL Server?

In MS SQL Server, update the maximum server memory configuration using either SQL Server Management Studio (SSMS) Explore or T-SQL query.

Methods are listed below:

  1. By Using SQL Server Management Studio (SSMS):
    Open SQL Server Management Studio (SSMS) from programs menu.
    Connect to the SQL Server instance with SQL server hosted machine using IP or Name, to list the jobs.
    In the Object Explorer, right-click on the server instance and select “Properties”
    In the Server Properties window, navigate to the “Memory” page on the left side.
    Under the “Maximum server memory (in MB)” section, enter the desired value for the maximum memory allocation.
    Click “OK” to apply the changes.
    Restart the SQL Server service by right click on server instance or restart the machine for the changes to take effect.
  2. By Using T-SQL query:
    — Set the maximum server memory configuration
    USE master;
    EXEC sp_configure ‘show advanced options’, 1;
    RECONFIGURE;
    EXEC sp_configure ‘max server memory (MB)’, 4096; — Replace 4096 with your desired value
    RECONFIGURE;
  3. By using SQLCMD:
    From the command prompt start sqlCmd using the Dedicated Administrator Connection (DAC). :
    net start MSSQLServer /mSQLCMD /f
    C:>sqlcmd -S ServerName -U sa -P –A Once you are connected, execute the following to set the memory to 4 GB, it should be enough to allow you to reconnect using the Management Studio tool.

    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘max server memory’, 4096;
    GO
    RECONFIGURE;
    GO

    Issues can be fixed by above process:
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
    There is insufficient system memory to run this query. Accidentally turned down SQL memory too low to log in, how do I fix?


by

Comments

2 responses to “How to update the Max Memory at SQL Server?”

  1. Haircuts Avatar

    You helped me a lot by posting this article and I love what I’m learning.

  2. Dryer Vent Cleaning Avatar

    Thanks for your help and for writing this post. It’s been great.

Leave a Reply

Your email address will not be published. Required fields are marked *