sqlservr.exe high memory usage for SBSMonitoring process

This seems to be a common issue with my SBS2003 installs. The administrative user will get an E-mail entitled: Allocated Memory Alert on 'ServerName' which directs you to the process table to find the culprit. In most of my cases I see two hogs. store.exe (Exchange) and sqlservr.exe. We will cover the issues surrounding store.exe in a later post, but for know here are my finding for the sqlservr.exe process. I found the fix in this thread on winserverhelp.com.

First verify that the sqlservr process at fault is in fact the SBSMonitoring one. First open Windows Task Manager, choose the processes tab, click the view menu and choose Select Columns. Check the PID column. This will show you the process ID of the entries in the process table. Sort this list by Mem Usage column and get the PID of the offending sqlservr.exe process. The to confirm the PID is SBSMonitoring at a command prompt type:

tasklist /svc

Look for the PID that matches your memory hog. It will look something like this. My PID was 376:

sqlservr.exe 376 MSSQL$SBSMONITORING

If this is the case, you can alter the memory settings for SBSMonitoring process with the following commands from the command line. (Thanks to David Copland)

osql -E -S servername\sbsmonitoring

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'max server memory', nnnn
reconfigure with override
go

My session looked like this:

C:\Documents and Settings\Administrator>osql -E -S file-server\sbsmonitoring
1> sp_configure 'show advanced options',1
2> reconfigure with override
3> go
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Configuration option 'show advanced options' changed from 0 to 1. Run the
RECONFIGURE statement to install.
1> sp_configure 'max server memory',100
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 100.
Run the RECONFIGURE statement to install.
1> quit

I set my server to use a maximum of 100MB of memory, and so far all seems well with it. I am planning on adding this to the normal maintenance cycle for all of our supported SBS2003 servers.

Till Next Time,

Sean Riley
President, DogRiley