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:
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:
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
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:
Repeat the following procedure for each database:
Right click on the name of the SQL Instance and choose properties:
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.
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.
Jeremy says
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.
jjporee says
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
BK says
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!
Max says
Thanks heaps for this. The WID database was the culprit in my case! Dropped my memory usage from 2GB down to 700MB! Crazy!
Stefan Masic says
Dear Sirs,
thank you very much. You save me a lot of memory.
With kind regards, ล tefan Masi?.
Julio says
Yes sir, thank you for this wonderful help. It did the trick…
Jamie McEwan says
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.
Billy Fury says
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
Lero says
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.
๐
Akeem says
You, Sir, are a fr*ggin G.E.N.I.U.S – thx so much!
Works like a charm! ๐ #hugs
Mark says
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.
ATX says
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 ?
Kim says
@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.
ATX says
Thanks !
I succeeded in change memory allocation for SQL, using “run as administrator” for SQL Server Management Studio and login as Domain admin.
cosplay costumes says
Thanks for any other great article. The place else could anyone get that type of information in such an ideal method of writing?
I’ve a presentation subsequent week, and I am
on the look for such information.
Dave Turner says
Thank you so much for posting. I’ve reclaimed 50% of my SBS 2011 server speed after limiting the RAM usage of the SQL instances. Great tip.
Kien Cao-Xuan says
Brilliant. I was dreading the day my machine burns out. With thism it may last few more months ๐
THANK YOU!!!