Limiting memory usage for SQL Server 2005 Express Edition

Limiting memory usage for SQL Server 2005 Express Edition

Method 1:

sp_configure: Limit SQL Server memory usage

Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot set max server memory to a value less than 4 MB. 16 MB or more is recommended especially if you are using replication. max server memory is an advanced option. You need to enable advanced options before you can use it.

Run the following SQL statements from the SQL window in any database manager to limit the amount of server memory used to70 MB:

Enable advanced options:

USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

Set the maximum amount of memory to70 MB:

USE master
EXEC sp_configure 'max server memory (MB)',70
RECONFIGURE WITH OVERRIDE

Display the newly set configuration:

USE master
EXEC sp_configure 'max server memory (MB)'

Set 'show advanced options' back to default:

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Method 2:

First, go with your Task Manager and under View -> Select Columns check PID and Virtual Memory Size.
Then see PID for the sqlservr.exe process which consumes large amount of RAM.
Then go to CMD Command prompt and enter this command:

tasklist /svc | find "sql"

You'll get something like this:

sqlservr.exe                  1621 MSSQL$ACCESSCONTROL
sqlservr.exe                  1725 MSSQL$PRINTLOGGER
sqlservr.exe                  1725 MSSQL$GLOBALEVENTLOG
sqlwriter.exe                 2950 SQLWriter

If this is true, you may limit it via Command prompt entering these commands (what you need to type is marked bold):

C:>osql -E -S YOURSERVERNAME\PRINTLOGGER

1> sp_configure 'show advanced options',1
2> reconfigure with override
3> go

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

1> sp_configure 'max server memory',70
2> reconfigure with override
3> go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'max server memory (MB)' changed from 2147483647 to 70.
Run the RECONFIGURE statement to install.

1> exit

Note: Replace YOURSERVERNAME and number 70 with values to match your system!
As you see, by design SBS monitoring database has a limit of 2147483647 MB of memory, so it can eat up practically all your RAM!

The 70 MB limit here shown is just a guess, and our recommendation is to use some value between 70 and 300, depending on your free RAM.
Have your Task Manager open and watch your sqlservr.exe process how it releases RAM immediately after you issue these commands. Many people have used this method and all are happy with server behaviour. No side effects have been noticed.