See Also:
http://www.printlogger.com   Printer accounting software
http://www.sys-monitoring.com   System monitoring software
http://www.hs-lab.com   Software for Business and Home
http://win-printing.com   Windows Printing Software,
Articles and Press Release
     
Handy Software Lab's Software Products
     
HSLAB Force Down   HSLAB Print Logger Family
HSLAB Shutdown Folder   HSLAB Sys Monitor
HSLAB Logger   HSLAB Prefetch Manager
HSLAB HTTP Monitor Family   HSLAB WhoIs
HSLAB Security TrackerFamily    
     
     

Common questions

ID #1112

How to limit RAM usage for SQL 2005 Express database?

 

This article applies to:

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?
Find the PID from Task Manager in the list from this command. Most probably it is MSSQL$PRINTLOGGER process which is guilty for large RAM consumption.

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.

Tags: -

Related entries:

Last update: 2010-06-19 19:07
Author: Oleg
Revision: 1.9

Digg it! Share on Facebook Print this record Send article to a friend Show this as PDF file
Please rate this article:

Average rating: 5 (1 Vote)

completely useless 1 2 3 4 5 most valuable

You cannot comment on this entry