SQL SBSMonitoring High Disk Usage
Ok so I’ve done my fair share of SBS migrations in my time and there’s one thing which has been bugging me for a while and that was the massively high disk usage that comes with the SQL SBSMONITORING instance on SBS2008 and SBS2011. It eats up disk usage like anything!
Now you ‘can’ get around this by simply disabling the SQL service (which I have been known to do), which solved the issue in the short term, but it is there for a reason and some programs require it such as backup programs in order to get a full 100% backup and so it needs to be running.
So while having a little free time I set about looking for a solution. But first here’s a screen shot from the Resource Monitor on SBS2011 showing you the Disk Usage of the SQL instance:
As you can see the ‘Read’ column and how high the sqlservr.exe image is compared to everything else. I know it only say’s 2 MB/sec Disk I/O but it’s the amount of Read (B/sec) which kills the disk speed, with the heads constantly jittering about reading data.
(This particular sqlservr does relate to the SBSMONITORING instance, if you hover your mouse over it’ll expand and show you the name.)
Basically the Database needs to be rebuilt, and luckily enough there is a nice little script to do this which you can find here:
Once you’ve downloaded this expand the contents extract the files to a ‘temp location’ then following the steps below depending on your Server edition:
NOTE: For this script to run without error you’ll need to stop the SQL Server (SBSMONITORING) and Windows SBS Manager Services. Or you can open up the Create-SBSMonitoringDb.ps1 script and add –force to the following lines as per below:
Stop-Service DataCollectorSvc -force
Stop-Service ‘MSSQL$SBSMONITORING’ –force
NOTE 2: You may need to unblock the script to enable it to be run. if you need to do this, open the properties of the Create-SBSMonitoringDb.ps1 file and click unblock at the bottom:
Ok so on with the fun stuff….
- Launch PowerShell as an Administrator
- Launch the script by typing:
- After completing the script it’s recommended to wait 48 hours before verifying that monitoring is working,
- Launch “C:\Program Files\Windows Small Business Server\Bin\MoveDataPowerShellHost.exe” as an administrator
- Launch the script:
So you can see the result above, 1 Row affected and everything looks good, the SQL Service and the Windows SBS Manager services have started.
Give it about 20-30 Minutes for the server to get settled and everything should be looking a lot better and faster:
I hope this gets you out of a sticky Quibble.
Fantastic, worked a charm. I have been having issues with a backupexec client taking 14 hours to process the C: drive on a SBS 2011 instance, this fixed the issue and now takes 1 hour to complete.
I’ve got the same issue and would like run this on my SBS 2008 SP2 system but I’m not finding the MoveDataPowerShellHost.exe on my server..
Please help… Thanks
Hi Scott, SBS2008 doesn’t have the C:\Program Files\Windows Small Business Server\Bin\MoveDataPowerShellHost.exe, this is only for SBS2011 so you don’t need this part, just follow the steps under SBS2008 heading in the article. Hope this helps sort it.
Thanks Dan, I ran the steps under 2008 and it appears to have run correctly based on the screen shot above
Do I need to do anything else like a reboot etc..
No worries Scott, you could probably get away with just restarting the “SQL Server (SBSMONITORING)” Service, but if you have a chance to reboot without disruption, schedule it. Dan
Cheers for this, have an SBS install that runs really well , but gets hammered by .disk activity and grounds to a halt
2 support companies couldn’t diagnose the issue, ultimately we were over working the server a bit, but it was still running really well 95% of the time with disks hardly touched, it would then work into a frenzy and lock up.
Stopping the SQL monitoring stopped the issue, but was unsure if it was long term fix
Just run the script and will see how it goes
Cheers for posting James, please let us know how it goes.
Thanks again for the post
5 days on from applying the script and it has resolved an issue that was driving me nuts for about 5 months & 2 support companies couldn’t solve, I was sure it was down to a maintenance issue on an SQL Database, but ran out of knowledge there!
Disk load on the server has dropped about 75% and no more occasional lock ups (we had 2 or 3 a day when it was at its worst)
We knew the SATA drives werent really upto the job, but now the server is perfectly usable in the short/medium term until we upgrade
No worries James cheers for the update, glad to help.
Thank you SOOOOO much for this. I was having problems with our ERP system going into not responding for several seconds and was at my wits end on what was causing it. I happened to see the that the SBS Monitoring Database was killing my hard drive every 30 minutes or so a day, and it coincided with the ERP lock ups.
I ran this script, and everything is working beautifully!
This is awesome! I was having problems with my ERP system locking up while my sales people were taking calls. Very frustrating. I noticed that SBS monitoring was hammering my hard drives every 30 minutes or so. Did a quick search, and came across this article.
This absolutely 100% fixed my problem.
Thanks for this!
No problem Mark you’re welcome, another problem solved 🙂
I get an exception error when running the script –
Exception calling “LoadForm” with “1” argument(s): “Could not load file or assembly ‘file://c:\program files\windows small business server\bin\MonitoringCommon.dll” or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.”
current MonitoringCommon.dll version: 6.1.7900.6 Dated: 10/8/2013
I can’t say I’ve come across this error, does the file exist in c:\program files\windows small business server\bin\MonitoringCommon.dll ?
Sorry, my fault. I was calling the script from the wrong TEMP folder.
Darin, you stat you were calling the script from the wrong TEMP folder.
Specifically how /where was the script called when it did work?
You saved me !!!
I was nearly crazy about an SBS server going SLOOOOWWWWW on a 20 SAS HDD RAID 10 SAN array !!!
This script is a real miracle !
Thanks for sharing !
Top stuff and no worries, glad it’s all resolved! Dan
getting the same error as the other guy,
Exception calling “LoadFrom” with “1” argument(s): “Could not load file or assembly ‘file:///c:\program files\Windows small business server\bin\MonitoringCommon.dll’ or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.”
At C:\Windows\Temp\Create-SBSMonitoringDb.ps1:64 char:39
+ [system.reflection.assembly]::LoadFrom <<<< ("c:\program files\Windows small business server\bin\MonitoringCommon.dll
+ CategoryInfo : NotSpecified: (:) , MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
New-Object : Cannot find type [Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContent]: make sure the a
ssembly containing this type is loaded.
At C:\Windows\Temp\Create-SBSMonitoringDb.ps1:67 char:28
+ $reportContent = new-object <<<< Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContent($null)
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
Any ideas? I've tried it on 2 different sbs 2011 machines and get the same thing.
Hi David, Darin who had this same error found that he was calling the script from the wrong TEMP folder. i.e. where the Create-SBSMonitoringDb.ps1 is located. Dan
SBS 2011 Server jamming up constantly!
This solved the problem INSTANTLY!!
We have SBS 2011 on a Hyper-V 2012 cluster and noticed slow response times from our SBS 2011 server. This fix reduced disk I/O from 70 MB/sec to 6 MB/sec and where before highest active time was always pegged at 100%, it now rarely exceeds 30%. I’m thinking this issue may have been slowing down all of our virtual servers, as the SBS monitoring service was monopolizing I/O resources on the SAN. Thanks for this awesome tip!
Just ran the script, from the previous comments I have a good feeling this will solve the problem I was shown! SQL Server going nuts, and bringing the SBS2011 server almost to a halt.
Thanks for this! I can sleep again.
when i run the script file on my SBS2011 server I am getting:
cannot be loaded. the file C:\windows\temp\create-sbsmonitoring.ps1 is not digitally signed. the script file will not execute on the system. Please see “get-help about_signing” for more details.
You may need to unblock the script to enable it to be run. if you need to do this, open the properties of the Create-SBSMonitoringDb.ps1 file and click unblock at the bottom. Hope this helps
That worked, my disk queue times went from 96.00 to 0.00.
what does the script do?
It simply backs up the existing SBS sql database and then creates a fresh one. Glad it helped.
Great article, and still up-to-date fix for SBSMonitoring high IO disk usage 🙂
Super, still just about needed 😉