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
No comments:
Post a Comment