MSSQL Database – Limiting RAM Usage

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.
And that’s it.  The SQL Server with reconfigure the RAM usage on the fly.

Move WSUS SQL database to another location

When running WSUS on a Small Business Server, the default location is the C: drive, when that drive gets full you need to move the content and the SQL database. Below are the steps to move the SQL database to another location.

1. Open command prompt and type: net stop “update services”

2. Next, at the command promt type: net stop w3svc (if it is SBS 2008 it will also stop the Terminal Services Gateway, remember to restart)

3. Open Microsoft SQL Express Management Studio (if you don’t have this, go to http://tinyurl.com/ynl9tv to download) and connect to the MICROSOFT##SSEE database, which is the WSUS database and the SharePoint databases in SBS 2008. You can’t just connect to the database normally, you need to type this in server name area:   \.pipeMSSQL$MICROSOFT##SSEEsqlquery

1577_1

 4. Detach the SUSDB database, move the SUSDB folder to the new location and attach the database again with Management Studio

5. Restart the services: “update services”, “w3svc”, and “Terminal Services Gateway”

 

 

How to find your version of SQL Server

You have 2 options.

  1. You can run the following SQL query:

    SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

  2. You can check the version of the sqlservr.exe file, below is a list of how the version breaks down:

    SQL Server 2005

    SQL Server 2005 RTM 2005.90.1399
    SQL Server 2005 Service Pack 1 2005.90.2047
    SQL Server 2005 Service Pack 2 2005.90.3042

    SQL Server 2000

    SQL Server 2000 RTM 2000.80.194.0
    SQL Server 2000 SP1 2000.80.384.0
    SQL Server 2000 SP2 2000.80.534.0
    SQL Server 2000 SP3 2000.80.760.0
    SQL Server 2000 SP3a 2000.80.760.0
    SQL Server 2000 SP4 2000.8.00.2039