Monday, 22 October 2012

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

5 comments:

  1. that link has pictures of cars. Is there an official link to sbsmonitoring.zip

    ReplyDelete
  2. I replaced that broken link with a working one.

    ReplyDelete
  3. When I try to run osql -E -S MYSERVERNAME\sbsmonitoring I get "Error: No user selected. Try with -U or -E switches?

    ReplyDelete
  4. Thanks mate, everything functionally correctly again.

    ReplyDelete