Accidentally set max server memory to 0

Question

Post reply

Accidentally set max server memory to 0

simon.wardlaw

Old Hand

Points: 317
More actions
February 4, 2011 at 5:57 am

#233177

I accidentally set max server memory to 0. Now I cannot rectify as there are insufficient resources in internal memory pool to rectify. Anyone know how I can recover? I've been unsuccessful to date in running sqlcmd in single user mode.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

have you tried starting SQL with the -f switch?

-f

Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. For more information, see the description for -m that follows.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

simon.wardlaw Old Hand Points: 317 More actions · Answer 2

Thanks for the reply. Have started the service in single user mode with -f;-m"SQLCMD"; however login fails when connecting through sqlcmd. I'm working on the console and loged in as local administrator and the service is started uner the same name.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

simon.wardlaw (2/4/2011)
Thanks for the reply. Have started the service in single user mode with -f;-m"SQLCMD"; however login fails when connecting through sqlcmd. I'm working on the console and loged in as local administrator and the service is started uner the same name.

What error? Login/Password not correct or cannot use as server is in single user mode?

If it's the single user one, someone got in before you. Stop/restart it again and shut off anything that pings for a connection (IIS servers and the like) so you can make sure you get the connection.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

simon.wardlaw Old Hand Points: 317 More actions · Answer 4

Thanks for the help. It seems by setting max server memory to ZERO, the system sets to lowest value of 16mb - which is insufficient. Running sqlcmd -A allows a connection but not enough resources to run commands. Running in single user mode proved difficult. I was successful only by running sqlserv at the command prompt : sqlserver /f /m"SQLCMD" (must be in caps). Another command prompt for sqlcmd. The parameters -A, -U, or no parameters failed to connect. Only the sqlcmd -E succeeded.

John.Sansom SSCertifiable Points: 7335 More actions · Answer 5

Try connecting to SQL Server using the Dedicated Administrator Connection (DAC)

See Using A Dedicated Administrator Connection

John Sansom (@sqlBrit) | www.johnsansom.com

rondallb SSC Enthusiast Points: 121 More actions · Answer 6

Start SQL Server configuration manager and stop the sql instance. Then, modify the startup parameters by adding ;-m to the end and restart the server. Once you can log back into the server, stop the sql service for the instance and modify the startup parameters again, changing the ;-m to ;-f. Then, start the sql service and quickly start ssms and log in. You should now be able to modify the max memory setting. Then exit SSMS, stop the sql instance again, and modify the startup parameters by removing the ;-f. Now restart the sql service and you should be back to normal. This worked for me.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

Just so that everyone knows, this does not work in a clustered environment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

Jeff Moden (5/18/2015)
Just so that everyone knows, this does not work in a clustered environment.

Quick question Jeff, would restoring the latest master be of any help, IIRC the Min-Max-Mem values are not stored in the registry but the master database.

😎

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

Eirikur Eiriksson (5/18/2015)
Jeff Moden (5/18/2015)
Just so that everyone knows, this does not work in a clustered environment.
Quick question Jeff, would restoring the latest master be of any help, IIRC the Min-Max-Mem values are not stored in the registry but the master database.
😎

Probably but it was a brand spanking new system. The step after this was to backup everything. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

axo959 Valued Member Points: 68 More actions · Answer 10

I was able to use the -f startup parameter on an active/active two-node cluster. Windows 2012R2, SQL2014.

I had to modify the registry by hand though to add a SQLArg3 of -f. sql config mgr seemed to be modifying the startup params (according to it), but looking in the registry the registry was not being updated.

The registry path:

computer\hkey_local_machine\software\microsoft\microsoft sql server\mssql12.sql001\mssqlserver\parameters

I'd watch the cluster admin. After disk & network came online and sql was pending, I'd immediately try to connect via ssms explorer. I didn't want anything else to get the single user connection avail while using -f.

Remember to remove the -f when done (via regedit), or sql agent wont start. Even though cluster admin will show disk, network name and sql online the role will still be 'failed'. (if sql agent is a resource in your role)

-Chris

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

That's excellent news. Thanks for posting it.

Still, I think I'll be a whole lot more careful in the future. Even if you can recover, it's a pain in the patooti. :pinch:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gonkite Valued Member Points: 69 More actions · Answer 12

SOLVED!

I made the same mistake. The solution is to start the service by adding the -f option

sqlservr.exe -f;-s INSTANCE

After that it allows you to connect from the SQL Server Management Studio and change the parameter Max Memory from the properties window.

Here is the source of this solution:

https://technet.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

kaharm SSC Enthusiast Points: 103 More actions · Answer 13

I followed the steps described in the following post and it worked for me!

http://www.naviant-inc.com/blog/sql-max-memory-limit-too-low/

Thanks,

Irina

webrunner SSC-Dedicated Points: 31724 More actions · Answer 14

Thanks for this thread. I realize that being careful is the way to avoid this issue, but my question is, why is this change even possible in SQL Server? The max memory setting should never accept a value below the minimum SQL Server needs to start up, shouldn't it? I just don't see the point of allowing 0 to be set for max server memory.

Just curious.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html