Problem Statement:
We recently migrated one of On-prem sql server to SQL server in Azure VM, As part of configuration settings, when we try to change the MAX memory of sql server, it accidentally changed the value to 128MB . We were unable to connect to sql server as sql server was not restarting due to low memory. This blog post explains steps that we followed the steps to fix this issue
- RDP to the server
- Make sure there are no other users RDP’d to the server, if they are, kick them off. You want to be the only administrator on the box.
- Open SQL Configuration Manager.
- Make sure all services are stopped. If they aren’t, right click stop them all.
- Right click SQL Server (InstanceName)>Properties>Startup Parameters tab>type -f in “Specify a startup parameter:”>Click Add
- This will add -f to the list on the bottom list (again not screenshotting that because I don’t actually want to change it right now).
INFO: The -f flag, 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.
OK out of this window. (It will warn you it won’t take effect until the next time SQL services are started, click OK on that box)
Open PowerShell ISE as administrator and put this code in the scripting pane:
net stop “SQL Server (InstanceName)”
net start “SQL Server (InstanceName)” -m”SQLCMD”
SQLCMD -S .\<InstanceName> -q "sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO"
If all the SQL services are turned off, you don’t need to execute the net stop line but is included incase the SQLCMD fails to get a connection, you don’t need to stop services from Configuration manager or services.msc.
Execute the Powershell code. I don’t have a good screenshot of the output or possible errors but if it throws any errors related to not being able to start the instance because its already running, or can’t connect to the instance during the SQLCMD part. Stop the script if its running, and run it again. I had to run it a few times (may have been due to trying to get all the code syntax right, or it was because something was making it in before SQLCMD (that service account had an RDP session open until I logged it off) could get a connection. Eventually you should see output that indicates the max memory was set and that you need to run a RECONFIGURE. Since the RECONFIGURE is part of the code to execute, it will execute, it just won’t output that the reconfigure was run. All done, Max memory is set to 4GB.
After getting Max memory set to 4GB, the instance is still up in single user mode and minimal configuration so all that’s left is to put it back to normal.
Stop the SQL services.
Remove the -f Startup Parameter.
Start all the SQL services.
Connect with SSMS and set the Max memory to what you actually want it to be.
Few additional notes:- The main idea behind why this works is a combination of net start “SQL Server (InstanceName)” -m”SQLCMD” and using a single SQLCMD command (connect to the instance AND run a query in the same PosH command) in PowerShell in the same script to be executed all as close to each other as possible. I suspect doing it from CMD prompt may not be fast enough as separate commands.
- Net start with -m”SQLCMD” should force the instance to ONLY allow connections from SQLCMD. That means no other application should be able to steal our single admin session before we can connect.
- we choose 4096 for the max server memory because we only wanted to get enough memory to start up the instance like normal and we could adjust to final max memory after the fact. we didn’t want to go overboard on the emergency. It is also beneficial because most servers should have at least this much space to be able to allocate so if we use the script for other instances, it’s one less thing to change to make it work.
It doesn’t matters what issue you are trying to solve but if we EVER need to get into an instance in minimal configuration and single user mode, PowerShell net start -m”SQLCMD” and SQLCMD -q, all in the same script executed at the same time is very likely your best friend due to it limiting the single user to someone trying to get in with SQLCMD and then running SQLCMD immediately after so you get the only connection.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQLZealots 