my solution - P.S.
|
Prev: SBS 2003 Pop3 Restrictions
Next: How do you block port 25 on workstations via SBS 2008/Group Policy?
From: Red Scourge on 5 Aug 2010 19:08 Another thing you may want to do, as I mentioned above it may start stealing a whole lot of memory on you, is lower the max memory usage of this database from within the SQL Management Studio Express: Connect to the SBSMonitoring database Right click the top item in the left hand side that is named according to your server name (not any of the sub items) > Properties Go to the Memory tab on the left side, and on the right side, drop the "Maximum server memory (in MB):" value to 64, then close all that stuff. Then just restart the SQL Server (SBSMONITORING) process in System Services (run services.msc or find in Administrative Tools), and watch in awe as your memory usage drops down to something reasonable (assuming it obeys this limit), regardless of how large the database grows to! Red Scourge wrote: My solution 05-Aug-10 I had this same problem. MDF file was 1.2GB, LDF file was 200MB, memory usage was about 1.2GB!!! I believe the problem stems from the fact that the WMI system collects far too many logs on many people's systems, SQL Server Express seems to stupidly try to to load the database mostly into RAM despite the fact that it's hardly ever read from and thus read performance doesn't matter. To make things worse, it does not even attempt to clear anywhere near a sufficient amount of old records, which also leads to massive disk usage, even in a network with as few as 10 users! After reading all about this I decided to try something similar, but I did things a little differently, and found the result to be WAY more effective. The basic summary of what I did was make a full backup of the data files just incase, then delete most of the rows from the dbo.WMICollectedData table in SQL Management Studio Express, THEN shrunk the database, then the files, then the database again, then on a hunch after examining the actual data stored in the above mentioned database table, I decreased the size of the system's logs in Event Viewer and set them to archive and keep instead of overwrite. Steps: Open SQL Server Management Studio Express > Connect to SBSMonitoring > Expand to Tables, dbo.WMICollectedData, right click, Properties (saw it was a MASSIVE table), I then realized WMI collected data is not critical to keep, and decided to delete the data. I deleted all but the last 200,000 rows or so (DELETE FROM dbo.WMICollectedData WHERE ID < (SELECT MAX(ID)-200000 FROM dbo.WMICollectedData), then went to do the shrink as others have suggested: Right-click on SBSMonitoring > Tasks > Shrink > Database > OK, repeat for Tasks > Shrink > Files, then repeat for Tasks > Shrink > Database yet again. After shrinking that database way down, I tweaked Event Viewer a bit: Start Menu > Right click Computer > Manage > Expand Diagnostics > Event Viewer > Windows Logs, then for each of the logs in there except Forwarded Events, right click the log > Properties > for "Maximum log size ( KB ):" Enter "10240", and for "When maximum even log size is reached:" choose "Archive the log when full, do not overwrite events". My hope in the last part is that the WMI Collected Events sounds like it's probably tied in with the Event Viewer somehow. If my instincts are correct, by not only limiting the max size more strictly and making it archive the old data instead of having huge limits and overwriting, perhaps it will help to keep the SBSMONITORING database nice and small (perhaps). Perhaps I am wrong and there is no correlation between the two, but in that case, if there's a way to add some sort of routine into the SBSMonitor database that attempts to limit the number of records in the WMICollectedData table, or perhaps export them somewhere for use by some log viewing program like Event Viewer, that would most likely alleviate this absolutely horrible issue. Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Make The WebClient Class follow redirects and get Target Url http://www.eggheadcafe.com/tutorials/aspnet/70511872-c3aa-4e92-a7d7-dd4b09881af5/make-the-webclient-class-follow-redirects-and-get-target-url.aspx |