Freigeben über

SQL Server can't start after accidently set the "max server memory" to 0

  • Artikel

One of my customer accidently set the "max server memory" of SQL Server to 0, and then the server can't start after that. If we try to start SQL Server from SQL Server Configuration Manager, it failed to start (esp. when you have several active databases), and in the SQL Server error log file, we could find a error message like "... a internal query can't be executed due to inefficient memory...". Or, sometimes, SQL Server can be started, but we can't create a connection to it with the following error message:

In this case, we could add -f to SQL Server startup parameters which allows SQL Server to start with minimum configurations therefore uses least memory. After SQL Server starts, we could connect to SQL Server using SQL Server Management Studio and update the "max server memory" to an appropriate larger value.

A bit more details: if we set the "max server memory" to 0 via SSMS-> Server's Properties -> Memory, SSMS will automatically adjust the value to minimum account of memory for option "max server memory"; for SQL Server 2005-2008 R2, it's16MB, and for SQL 2012, it's 64MB for 32-bit, and 128MB for 64-bit server box; if we set the "max server memory" to 0 via sp_configure stored procedure directly, SQL Server will automatically use default "max server memory" value which is 2147483647MB.

References:
Server memory server configuration options: https://msdn.microsoft.com/en-us/library/ms178067(v=sql.110).aspx.
How to: Start an instance of SQL Server: https://msdn.microsoft.com/en-us/library/ms180965(v=sql.105).aspx

Hope this helps!

Comments

  • Anonymous
    July 07, 2015
    Poorly written blog. Does not have any steps but links.

  • Anonymous
    September 08, 2015
    we set the "max server memory" to 0 via sp_configure stored procedure directly, SQL Server will automatically use default "max server memory" value which is 2147483647MB. wrong statement.  When tried to run sp_configure with Value 0, SQL is setting min default value that is  either 16MB or 64MB

  • Anonymous
    November 11, 2015
    SQL 2012 onward if you apply Max memory to '0', By default SQL takes 128 MB. SO you don't see this issue.

  • Anonymous
    November 27, 2015
    is's work fine.