Tuesday, 17 August 2010

SBS 2008 Monitoring Database Maintenance

If you find that the SBSMONITORING instance of SQL Server is using up more than its fair share of resources (1.5GB+ RAM) and the
"SBSMonitoring.mdf" and "SBSMonitoring_log.LDF" files are over 4GB in size you need to run a maintenance script on the database and then shrink it.

SQL Server Express supports a maximum data file size of 4GB - once this limit is reached the SBS monitoring stops working.

To resolve this download this sql script:

Run this script using:
Sqlcmd -S %computername%\SBSMonitoring -E  -i c:\path\to\updateSBSMonitoring.sql

If this runs successfully you should see:
Changed database context to 'SBSMonitoring'
(1 rows affected)

This can take a while to process (45min on a large database)

This will ensure that the database will contain no more than 90 days worth of data.

Source URL: http://blogs.technet.com/b/sbs/archive/2009/07/14/sbs-2008-console-may-take-too-long-to-display-alerts-and-security-statuses-display-not-available-or-crash.aspx

Next we need to shrink the database size:

Run the "SQL server management studio express
Ensure that "SERVERNAME\SBSMONITORING" is selected and "Windows Authentication" is used and click "Connect"
Expand Databases and find the SBSMonitoring, right click it and choose Tasks -> Shrink -> Database

This will run for a while (20min) after which the database size should reduce to a much smaller size (about 300MB or so in some cases)

Source URL: http://www.eggheadcafe.com/software/aspnet/35793646/sbs-monitoring-4gb-2008-now-what.aspx


  1. Keep in mind that running this script is going to send the size of your SBSMonitoring.log file through the roof. I've seen in other places that it may get as big as 20GB. Make sure you have enough space on the drive that has the log file before running the script!

  2. Given the hazards, if you can live without past data, the instructions on simply just shutting down the SBSMonitoring instance, renaming/moving the files and recreating them are simple and trouble free. They come from a link in your source article.

    I had only 12 GB of space remaining. I didn't want to take the chance on crashing the server by filling the C:\ drive!