SBS 2008 sqlservr.exe SBSMONITORING - high memory usage

What:

Microsoft SQL Server on Windows Small Business Server 2008

Problem:

Microsoft SQL Server is using huge amount of memory, makes server slow and unresponsive.

Solution:

First check which sql instance is using resources.
Start > Run cmd

tasklist /svc | findstr "MSSQL"

Take a note of PID number and find it in task manager (click on view/select columns options to make sure the PID [process identifier] option is selected).

Now when you are sure that SBS Monitoring is a problem follow below steps.

1. Run services.msc
2. Stop the Datacollectorsvc service (Windows SBS Manger Service) & SQL Server (SBSMONITORING) service (to unlock monitoring database files)
3. Change the names of those 2 files, instead of deleting them - just in case:

 C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring.mdf
 C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring_log.ldf
 to
 C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring-bak.mdf
 C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring_log-bak.ldf

4. Download sbsmonitoring file
5. Unzip the zip file and copy those two files to C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder
6. Start the SQL Server (SBSMONITORING) service
7. Start the Datacollectorsvc service (Windows SBS Manger Service)

Microsoft released kb with regards to this issue.

Force sql to use defined amount of memory.

Start > Run cmd

osql -E -S YOURSERVERNAME\sbsmonitoring  [hit enter]
sp_configure 'show advanced options',1 [hit enter]
reconfigure with override  [hit enter]]
go [hit enter]

sp_configure 'max server memory', ### [enter the value of the max. I used 90 or 200]  [hit enter]
reconfigure with override  [hit enter]
go [hit enter]

Source