Link to home
Start Free TrialLog in
Avatar of kraven1388
kraven1388Flag for United States of America

asked on

SBS2011 - Excessive Disk Usage - SQLSERVR.EXE

Hello Everyone
I have an SBS2011 box which has performance issues due to SQLSERVR.EXE.  If I kill the process hard drive activity is close to 0%.
Current view from task manager.
User generated image
I have adjusted the memory usage in SQLServMngt, reset, deleted, installed, uninstalled and a bunch of other things.  I was able to recover hard disk space, lowered CPU and Memory usage.  If it were not for this one issue the server would be working great.

The service mssql$microsoft##ssee service high disk usage
SQLserver.exe High Disk as shown in the image
susdb.mdf high disk usage
tempdb.mdf high disk usage

If I stop the SBSMONITORING service DISK USAGE the server goes normal.

Below are some the resources I have used to attempt to resolve issue.

http://www.itquibbles.com/sql-sbsmonitoring-high-disk-usage/
https://www.experts-exchange.com/questions/28270133/Sqlservr-exe-Using-too-much-disk-time.html
https://www.experts-exchange.com/questions/28707799/High-disk-utilization-Queue-on-SBS2011.html
https://www.3ait.co.uk/blog/solved-shrink-a-wsus-database-susdb-mdb-to-almost-nothing-2
https://community.spiceworks.com/topic/959769-sbs-2011-very-slow-performance-because-of-susdb-mdf
https://blogs.technet.microsoft.com/configurationmgr/2016/10/18/recreating-the-susdb-and-wsus-content-folder-for-a-windows-server-2012-based-wsus-computer/
http://www.howtodo.co.il/?p=840
Avatar of Radhakrishnan
Radhakrishnan
Flag of India image

Hi,

mssql$microsoft## is belongs to Windows Internal Database which used as WSUS db. What is the actual size of your content store?. Also, what's the size of the database folders?. This issue happens if the content store and database size is in huge. As a initial step, you can run the WSUS cleanup wizard which clear out the old definitions, unused updates, superseeded updates etc.

If the content store is high then you may need to move the content store to different server or different drive.
Define High?

The figures in the image total about 12.2MB/sec Read and 600KB/sec write.

A single spinning rust disk should be able to do 100MB/s and 100 IOPS at least (Enterprise disks even more)
Avatar of kraven1388

ASKER

@Radhakrishnan R
WSUSContent - 79.5 GB
SUSDB -35.6 GB
 User generated image
I will run the WSUS cleanup wizard and see if that helps to clear up issue as the DB and Content store are massive.  I have already moved the WSUS to the D:\ Drive.
I attempted to clean up WSUS but I keep getting failure below.  I have been reading and others recommend I reinstall WSUS.
User generated image
Hi,

Is the w3wp Apppool in WSUS stopped?
ASKER CERTIFIED SOLUTION
Avatar of Hypercat (Deb)
Hypercat (Deb)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So is the problem with performance or space?

The best way to find out what is consuming disk space is to use a visualisation tool like Treesize or Windirstat (make sure you run them as System though, otherwise they might miss hidden files)

Don’t spend too much time on trying to pare a system down though as it’s far quicker (and more cost effective) to just buy a bigger disk
Don't bother re-installing WSUS, that's a very last resort in SBS.

Restart the Windows Internal Database Service and try doing another WSUS Cleanup.  If its the first time that you've done the cleanup it will fail. But in the background, it will actually be cleaning up the database.  The trick is to do only one cleanup option at a time if it fails to complete then restart the Windows internal database service and restart the cleanup for that option.  Repeat this until it suceeds, eventually it will.  Then move onto the next option and repeat.

Once all options have been done successfully, re-run the WSUS cleanup once a month.

With regards to the actual Disk performance, unless its causing you an actual issue, this can be ignored.  The cleanup may ease this.
Hello Thank for the replies.  I have not had an opportunity to work on this server over the last couple of days.  I will reply shortly with what I have done and the result of your suggestions and screen shots of the current performance of server.
@HyperCat
SBS reports are not big of a deal to the customer and we are going to replace the server at some point in the next 2 months.  At this point I just want to know if I risk losing anything of importance by disabling MSSQL$MICRSOFT##SSEE as that is what is bogging down the system.
That is the WSUS database, so you DON'T want to stop that unless you want to have every user (and the server) do their own Windows updates on their workstations.  If you decide to do that, you have to be sure also to remove the group policies that point the server(s) and workstations to WSUS rather than local Windows updates management.

I would think a better option would be to clean up WSUS unless you really can't get it to work.  The sizes of those folders is outrageously bloated and obviously no one has clean it up for a very long time.  Have you tried the cleanup method I posted above in my comment ID42321378? The first thing in this process is to re-index the WSUS database, and I'm hoping that if you can get that process to run, it will clean things up to the point where you can get the rest of the process to work also.
@HyperCat
I will attempt to re-index and see what happens.
@HyperCat
Finally got an opportunity to perform indexing.  Let you know the results.
@HyperCat
I started the process of reindexing and left the server alone for a couple of hours.  I noticed that the process was frozen on the CMD line.  I stopped the process and then restarted indexing.  The CMD is once again frozen but the HDD activity appears to have stabilized as shown in the screen shot.  I will let the system do its thing and then see what happens.
User generated image
How long has it been running? Do you see any progress messages on the command line?  You should see some commands, including things like"Changed database to SUSDB" and then a series of lines showing "Updating [tablename.dbo]" with additional statements for each table that gets indexed. Are you seeing any of this activity?
@HyperCat
With success I have completed Indexing of WSUS; however, I am not able to perform cleanup of WSUS.  WSUS Update Service fails with message shown below.  Any suggestions on how to resolve as PS commands to resolve issue with WSUS fail as shown below.
User generated imageUser generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@hyperCat
Sorry but is the a scripting for dummies.  Not sure how to run this script.
Download the ZIP file and extract the script.  It has a file extension of .ps1.  Copy the script file into the Windows Powershell home directory: %systemroot%\system32\WindowsPowershell\Scripts.  Open Powershell and navigate to that location.  Then type the following command to run the script:

PS C:\Windows\System32\WindowsPowershell\Scripts>.\WSUS-Maintenance.ps1
@HyperCat
I attempted to run script but get the message below.
User generated image
Still confused on the problem here!

The original post was about performance, but this seems to have morphed into disk space issue.So what is the real Problem?

None of the capacity/performance figures look bad. Please tell us what the real problem is?
re script, obviously as a starter you need to edit the script to change SERVER-01 to the name of your server
@Gerald
The issue with the server is that the SQLServr.exe causes the activity time% to constatntly be at 100% causing performance to be very poor and some instances the server was unusable.  The only way to stabilize the server was to stop MSSQL$MICRSOFT##SSEE.  So after taking another look at my situation I believe that I will call it resolved.  The RE-Indexing of WSUS is what stabilized the server and that was goal.  WSUS snap-in continues to fail when you attempt to cleanup but the server has more than enough space to handle the increasing WSUS folder and at some point very soon the server will be replaced.  Thank you for everyone help.  


Follow the link below to document and also available in @Hypercat comment above.
https://filedb.experts-exchange.com/incoming/2017/10_w40/1197856/How-to-Remove-Unneeded-WSUS-Content.docx
Re-indexing stopped the constant 100% disk activity time %.  I have also now been able to do some cleanup from WSUS Console.  It still fails when running delete unneeded update files but all of the other options have worked.  Will continue to run until it works hopefully.