[MSSQL] Set max server memory using command | 用指令設定伺服器最大記憶體

Few days ago I am testing a issue, set maximum server memory to very low (min limit 128 if you set the value below it) on SSMS, after apply the setting, SSMS pop out error , broken connection from SQL server and can't connect back due to the memory to low can't accept connection.
幾天前我在測一個問題，用SSMS把DB的最大伺服器記憶體設到非常低(系統最低是128MB)，執行這個設定後，SSMS跳出錯誤，與SQL伺服器的連線中斷而且因為記憶體太少而連不回去。

I take some time to find out how to change the setting from command line, here is the guide.
我花了點時間找出怎麼用指令把設定改回來，下面是教學。

Step 0: Stop sqlserver at service|在服務裡把sqlserver關掉

I am not sure this step need or not, but I stopped sqlserver before start following step.
我不確定這步需不需要，不過我在做下面動作前有先停掉sqlserver。

Step 1: Run SQL on minimal configuration and single user admin mode|用最小組態與單一使用者模式啟動SQL

Go to your SQL server location(for me: "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"), execute
到你SQL server目錄下，執行

sqlservr -f -m

to start sqlserver, after it started, leave it on.
來啟動SQL server，啟動後，別關掉這個視窗

Step 2: Run SQL script to re-config setting|執行SQL指令重設設定

Open another command prompt execute
開另外一個cmd出來執行

sqlcmd -e

then run following script
來跑下面的指令

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sys.sp_configure 'min server memory', 1024;
GO
EXEC sys.sp_configure 'max server memory', 2147483647;
GO
RECONFIGURE;
GO

After that, the problem should be solved, you can try using SSMS connect to DB to test it.
一切順利的話，問題應該解決了，你可以試著用SSMS連到DB試看看。


Reference:
INCREASE SQL SERVER MAXIMUM MEMORY FROM COMMAND PROMPT
SQL Server Maximum Memory setting