Wednesday, January 3, 2018

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

sql_maxmemory3
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,啟動後,別關掉這個視窗
sql_maxmemory2


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
sql_maxmemory


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

No comments:

Post a Comment