SQL [sqlservr.exe] Memory Consumption on Small Business Server July 7, 2009

I started receiving alarms that pages/sec on my client’s SBS Server was 6952.92 pages/sec. Please note that Microsoft Best Practices recommends no more than 20 pages/sec.
I found that sqlservr.exe was consuming in excess of 1.5GB of memory. This is been a known issues for Servers running Small Business Server, I will walk you through the process of reducing usage for this memory thirsty process.

1. Open Task Manager > Processes Tab, View Menu > Columns, select PID check box.
2. In the list of processes locate the sqlservr.exe process that is consuming the most memory (there might be multiple instances of sqlservr.exe) take note of the PID number next to the memory thirsty process.
3. Open a command prompt and issue the following command without the quotes,
    “tasklist /svc” (this is used to display running processes (PIDs) and applications)
     next to the PID number previously identified in taskmanager take note of the service name.
   The service would be MSSQL$SBSMONITORING.

4. At the command prompt issue the following commands, after each command press enter;
   
 Command 1: osql -E -S SERVERNAME\sbsmonitoring  
(replace SERVERNAME with the hostname of your server, sbsmonitoring is the service name )
 Command 2: sp_configure ‘show advanced options’,1
 Command 3: reconfigure with override
 Command 4: go

Command 1: sp_configure ‘max server memory’, 200
(200 represent memory size in MB, replace 200 with any memory size you wish, default is 2147483648)
 Command 2: reconfigure with override
 Command 3: go