Every so often I log into a server to find that it is encountering errors due to it running low on Resources, usually RAM. If it is a Windows SBS or SQL Database Host, more often than not the issue is due to an SQL Database that just got out of hand. Most of the time it seems that the SQL Database has no Max Memory limit set. After many interactions where I have had to go into SQL Studio to try to set the limits. I recently discovered a set of commands to accomplish the same in 5 minutes or less. That is what I shall share.
PLEASE NOTE, WINDOWS SERVER 2008 + ONLY
The SQL Database I was working on when I encountered this fix was the Internal Database used for WSUS, so I will start with that example.
Start by opening up a command prompt, then type in the following and hit enter.
sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E
sqlcmd Flags Used:
- -S [protocol:]server[\instance_name][,port]
Example: sqlcmd -S tcp:*ComputerName*\*DatabaseName*,1433- -E (use trusted connection)
This will bring you to an SQL Prompt that in the Command Prompt window. Your line should be: >1
From here, we are entering a few SQL commands to run against the Database.
sp_configure ’show advanced options’, 1;
reconfigure;
go
sp_configure ‘max server memory’, 512;
reconfigure;
go
exit
sp_configure [ [ @configname = ] 'option_name' [ , [ @configvalue = ] 'value' ] ]
- show advanced options – Required to access Max Server Memory
- max server memory – Sets Max Memory in MB the Server can use
- min server memory – Sets Minimum Memory in MB the Server starts with
RECONFIGURE - Specifies that if the configuration setting does not require a server stop and restart, the currently running value should be updated GO - sqlcmd specific, Used to send all commands to SQL Database to execute.
