[SQL Server][Emergency]SQL Max Memory Limit Too Low

2017-07-22

9197
0
SQLEmergency
2017-07-25

由於SQL Server最大的記憶體限制預設是2,147,483,647 MB(超大的!而且很眼熟，整數int的最大值)，再加上傳說中SQL Server是愛吃記憶體的怪獸，為了避免她不小心把作業系統的記憶體都吃的很乾淨，甚至還使用到虛擬記憶體(可以設定Lock Pages in Memory避免)，在安裝完SQL Server後，我們會配置適合比例的記憶體給指定的Instance。因為想測試SQL 記憶體不足對交易效能的影響，不小心把記憶體設定成很小的375 MB，這下悲劇了。

SQL記憶體不足錯誤

SQL Server首先出現了SQL 701的錯誤:

訊息 701，層級 17，狀態 130，行 1

資源集區 'default' 中的系統記憶體不足，無法執行此查詢。

想要改回來，也沒得改，欲哭無淚!

-- Turn on advanced options
EXEC  sp_configure'Show Advanced Options',1;
GO
RECONFIGURE;
GO

-- Set max server memory
EXEC  sp_configure'max server memory (MB)',6400;
GO
RECONFIGURE;
GO

.Net SqlClient Data Provider: 訊息 701，層級 17，狀態 130，行 1

資源集區 'default' 中的系統記憶體不足，無法執行此查詢。

這時不用緊張，也不用重新安裝SQL Server，想起Super SQL Server楊老師才在幾週前有分享【管理 Life Saver 】之【調整記憶體時候看錯MB為GB 導致無法啟動SQL Server】，就像茫茫大海迷失方向找到明燈:

啟動SQL Server於-f -m模式

1.停止SQL Server服務以及TCP/IP網路連線

打開SQL Server Configuration Manager熱鍵: Windows 鍵 + R + sqlservermanager12.msc

後面12是版本號，如果是SQL Server 2012則為11，SQL Server 2016則是13

停止對應的DataBase Engine，案例中的Instance是叫SQL12。

關閉TCP/IP通訊協定，暫時限制只有本機連線(Shared Memory)登入。

2.以最小資源啟動SQL Server並且修改記憶體組態

以系統管理員身分打開第一個命令提示字元:

切到SQL Server \Binn目錄

cd E:\SQL2014\MSSQL12.SQL12\MSSQL\Binn

視安裝時選擇的目錄。

以最小組態並以單一使用者啟動SQL Server，指定SQL12的Instance，限定使用SQLCMD程式連接。

sqlservr -f -m"SQLCMD" -s SQL12

Starts an instance of SQL Server with minimal configuration –f
Limits the connections to a specified client application -mSQLCMD
named instance of SQL Server -s "SQL12"

OK! SQL Server啟動了，我們先不要關閉這個視窗，現在SQL Server目前可是透過她執行的。

打開第二個命令提示字元

以sqlcmd登入指定的Named Instance

Sqlcmd -S .\SQL12

(注意，-S的S是大寫)

先確認Instance Name是否正確，有的時候打錯，她就登入到Default Instance

執行以下T-SQL命令修改最大記憶體使用組態

-- Turn on advanced options
EXEC  sp_configure'Show Advanced Options',1;
GO
RECONFIGURE;
GO

-- Set max server memory
EXEC  sp_configure'max server memory (MB)',6400;
GO
RECONFIGURE;
GO