sqlservr.exe using 24GB of RAM for an inaccessible database

Question

A server running SBS 2011 is causing me all kinds of headaches. All but 500MB of the 24GB of RAM is in use, mainly for sqlservr.exe. My first idea was to fix the amount of memory SQL could use. But in SQL Server Management Studio, the server can't connect to the database on itself. When the SQL Server Configuration Manager is launched, "Connection to target machine could not be made in a timely fashion." I feel like I'm out of options, every action takes minutes to process because of the low memory.

It's not clear if this database is being used elsewhere in our system so killing it isn't an option. Is there a direct way to limit the memory usage? The underlying problem is likely an authentication issue but I can't solve it when a window takes 5 minutes to load. Thanks for any tips in advance

Answer 1

If you can't connect with SSMS or SQL Server Configuration Manager, you're gonna have a lot of problems. But let's see what we can do to relieve the memory pressure so you can proceed with troubleshooting.

You'll want to start SQL Server in "minimal configuration" mode. If you can't get SQL Server Configuration Manager to work, you'll need to either:

• modify the startup parameters of the service using sc.exe and restart the service; or
• stop the instance from the Services control panel and start the instance from a command prompt

The parameter you want to add is -f.

-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.

Depending on where your binaries are installed, it'll look something like this:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\binn\sqlserver.exe -sMSSQLSERVER -f

This will start SQL in a minimum configuration so you can work on figuring out your authentication issue.

Caution: any client connectivity will be disrupted, so do this during a maintenance window.

Answer 2

Absolutely don't kill the sqlserver.exe process. That is the best way to end up with a corrupted database.

You need to restart the SQL Server service (or even the whole server); this should free you from the low memory condition and allow you to perform proper troubleshooting and tuning.

You can check if anyone is actually using the database by looking for open connection on TCP port 1433 on the server:

netstat -nao | find ":1433"

This should give you a quick glimpse about who (if any) is using the database; this will allow you to at least estimate the impact of stopping the service for a few minutes.

Until you are able to restart the service, you can't do anything; not only because the server is unusable, but also because the configuration parameters for limiting memory usage only take effect after a service restart.