High Memory usage in SBS 2011 caused by SQL Server Databases (DataCollectorSvc.exe)

SBS 2011 in it’s default configuration will experience very high memory usage caused by the three SQL databases that are running and in my experience will eventually cause poor performance where physical memory is being consumed by these databases. In particular I have noticed extremely High memory usage in SBS 2011 by the DataCollectorSvc.exe process, it was using around 12 GB!

There are three databases:

  • SBSMONITORING
  • SHAREPOINT
  • WINDOWS INTERNAL DATABASE (MICROSOFT##SSEE)

All three of them have the potential to consume a great deal of memory and by default the memory limit on these instances is set to 2147483647 MB – which is 2048 Terabytes. Given that SBS 2011 supports a maximum of 32Gb of memory, restricting the memory usage on the databases can give you some of your memory back and improve the performance of the machine.

Because all three databases are separate instances you need to connect and modify each one separately.

We can use one instance of SQL Server Management Studio to manipulate all three instances, which makes things a bit easier. Open SQL Server 2008 Management studio by doing the following:

Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Right click on “SQL Server Management Studio” and choose, run as administrator:

How to start SQL Server Management Studio in SBS 2011 as Administrator

Reduce memory usage of the SBSMONITORING, SHAREPOINT and Windows Internal Databases

Once SQL Server Management Studio has opened you will be prompted to connect to a server, enter SERVERNAME\SBSMONITORING. In this example the server is simply called SBS:

Connect to SBSMONITORING Database with SQL Server Management Studio

Once connected to your database, connect to the other two databases at the same time by going to File -> “Connect Object Explorer”. This time in the server name enter:

SERVERNAME\SHAREPOINT

Connect to SHAREPOINT database on SBS 2011 with SQL Server Management Studio

Once again to go File -> “Connect Object Explorer”. Enter:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

All being well you should now have SQL Management Studio with all three databases listed like this:

SBS 2011 - SQL Server Management Studio connected to SBSMONITORING, SHAREPOINT and Windows Internal Databases

 

Repeat the following procedure for each database:

Right click on the name of the SQL Instance and choose properties:

Modify SQL Instance Properties in SBS 2011 using SQL Management Studio

Click on the “Memory” option on the left hand side and adjust the memory figure as appropriate for your system. The system in this example has 32Gb of physical ram so I have chosen to give each database 2Gb. If you have less memory you will need to adjust according to your own system.

Setting the maximum memory usage for an SQL Instance on SBS 2011

After you have adjusted all three databases you can quit SQL Server Management Studio and then restart the following services for the changes to take effect:

  • SQL Server (SBS Monitoring)
  • SQL Server (Sharepoint)
  • Windows Internal Database (MICROSOFT##SSEE)

Hopefully after doing this SBS 2011 should run a bit smoother, on this system the SQL Databases were using over 16Gb of Ram and it was running very slowly.

Hope this helps.

14 thoughts on “High Memory usage in SBS 2011 caused by SQL Server Databases (DataCollectorSvc.exe)

  1. This is great! Exactly what I was looking for, and worked like a charm. I limited it to just the DataCollector DB, and it took about a minute for it to reflect the memory consumption change after restarting the SQL service, but all is good.

  2. Yes, it works !
    I could not connect to the SBSMONITORING service, only to the two others services; but still i got memory usage reduction.

    than you

  3. Thanks for taking the time to post that!

    I hadn’t considering the necessity of tweaking the memory settings of SBSMONITORING! Why would you have to? I mean SBS is supposed to be a turnkey product, right?

    Thanks a bunch!

  4. Thanks heaps for this. The WID database was the culprit in my case! Dropped my memory usage from 2GB down to 700MB! Crazy!

  5. Many thanks for this. It’s helped me deal with the DataCollectorSvc.exe hogging memory in a far more elegant way than I was previously.

  6. That worked for me 16GB memory and running at 97% usage. Backups failing due to timing out. Now down to 57% with max memory settings at 2048MB.

    Many thanks for this solution.
    Billy

  7. Thank you very much!
    I’d already limited the ram amount taked by Exchange, but it not was enough.
    Now the system appear to be perfect.
    😉

  8. Thank you! I’ve been looking on how to connect to the SSEE DB for a while now. my stupid way of dealing with this issue was to schedule a task to stop and start the service a couple times a day. This is perfect! thank you.

  9. I tried to change memory allocation, but without success !
    The settings were refused because not rights.
    I connected as Domain administrator.
    How can I login as “sa” ? What is PW for “sa” in Windows SBS 2011 ?

  10. @ATX use Windows Authentiation instead.

    In the last 600 (of 6000) old posts to delete from WSUS, so I will not change it now having an execution running. But hopefully above will be as usefull to me as to the others. Thanks before.

  11. Thanks !
    I succeeded in change memory allocation for SQL, using “run as administrator” for SQL Server Management Studio and login as Domain admin.

Leave a Reply

Your email address will not be published. Required fields are marked *