jump to navigation

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

Posted by Gomez in IT, Microsoft, Performance & Alerts.
Tags: , , , ,
trackback

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

 After the last command “Go” you should see a confirmation that DBCC execution has completed. You can just type exit at the command prompt to exit.Open Task Manager > Process Tab and review the sqlservr.exe and confirm that memory usage has decreased.
Best of luck..
Advertisements

Comments»

1. BeanAnimal - June 23, 2011

Wonderful! This is the first time I have seen this resolution to the SBSMONITORING sql issue.

2. tedvdb - May 4, 2012

Thanks, worked perfectly! Decreased memory usage of sqlservr.exe from >800Mb to 200Mb.

3. G - September 17, 2014

Thanks very much, from 2GB to 250mb!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: