SBS 2008 Monitoring Database Fills to Capacity 15

Post to Twitter Post to Facebook Post to StumbleUpon

One of the differences between SBS 2003 and SBS 2008 is how the monitoring database is handled. In SBS 2003 the database did not have a size limitation and was able to grow as large as required. In SBS 2008, this is no longer the case. SQL Express has a 4GB limit and the other day one of my clients hit it.

So how do you know when your SBS Monitoring database is full? You’ll get this error in the Application Log:

Log Name:      Application
Date:          3/19/2010 7:36:29 AM
Event ID:      1827
Task Category: (2)
Level:         Error
Keywords:      Classic
User:          SYSTEM
Computer:      SBS2008.domain.local
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

It’s pretty clear from the message what the problem is. So what caused it and how do we fix it?

The cause is that the database is not configured to purge old data. There is a fix for this and it turns out to the same sql script that is used for an SBS problem where the Console displays too slowly. Run this script against your SBS Monitoring database and it will only hold data for the last 90 days. So if your database isn’t completely filled up yet you’ll want to run this now.

NOTE: The following instructions and script link from from the Official SBS Blog posting SBS 2008 Console May Take Too Long to Display Alerts and Security Statuses, Display Not Available, or Crash.

  • Download the following file to the server you are going to be working on:
    2. We recommend you save the file to an easy to access path, such as c:windowstemp.
  • Complete a backup of the SBSMonitoring Databases
    1. Open Services from Administrative Tools, Services
    2. Accept the UAC prompt
    3. Find and stop the SQL Server (SBSMONITORING) service.
    4. Make a copy of the files in the following folder:
      1. C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLData
    5. Once the file backup is completed, start the SQL Server (SBSMONITORING) service.
  • Proceed to run the following set of commands to implement the improvements
    1. Open an Administrative Command Prompt (Run As Admin)
    2. Type the following command, substitute the path to the updateSBSMonitoring.sql file as needed (We recommend that you do NOT copy & paste this command directly from the blog post):
      1. Sqlcmd -S %computername%SBSMonitoring -E  -i c:windowstempupdateSBSMonitoring.sql
      2. Success will show an output like this:
        Changed database context to ‘SBSMonitoring’
        (1 rows affected)
  • If your database is completely filled up like mine was, then you need to replace it with a clean empty one. I consulted out resident SQL expert, Edwin Sarmiento, for this advice. You’ll have to get clean empty database through one of 3 methods: Open a ticket with Third Tier, open a ticket with Microsoft, or stand up an SBS 2008 server and grab it from there.

    To install your new clean database do this:

    1. Open an elevated SQL Server Management Studio Express
    2. Connect to the <servername>SBSMonitoring database using Windows Authentication
    3. Expand Databases and right-click on SBS Monitoring
    4. Choose Tasks, then Backup
    5. Make a backup of the current database
    6. Next Choose Tasks, then Detach to detach the database from SQL
    7. Open Services.msc and stop the SQL Server (SBSMonitoring) and SQL Full Text Search (SBSMonitoring) services
    8. Open an elevated Windows Explorer and drill down to c:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLData and replace the SBSMonitoring and SBSMonitoring_Log files with the blank ones you obtained.
    9. Start the services that you stopped earlier
    10. Verify that you can run a report from the console

    Since you have now gone to the trouble of installing a new database, to insure that this doesn’t happen again run the above SQL script on the new database.

    So who wrote this blog and what do they do for a living anyway?

    We’re Third Tier. We provide advanced Third Tier support for IT Professionals.
    Third Tier Get Support BlogFeed Blog Twitter Twitter Facebook Facebook LinkedIn LinkedIN

    Leave a comment

    Your email address will not be published. Required fields are marked *

    This blog is kept spam free by WP-SpamFree.

    15 thoughts on “SBS 2008 Monitoring Database Fills to Capacity

    • Kevin Weilbacher

      I ran this on one SBS2008 server this evening, and received the following failed message:

      C:\Users\SBSAdmin\Downloads>sqlcmd -S %computername%\SBSMonitoring -E -i updateSBSMonitoring.sql
      Changed database context to ‘SBSMonitoring’.

      (1 rows affected)
      Msg 1913, Level 16, State 1, Server INTRANET\SBSMONITORING, Line 4
      The operation failed because an index or statistics with name ‘_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2’ already exists on table ‘dbo.WMICollectedData’.

    • Amy Babinchak


      As I understand it you will get this error if the script has already been run on the database previously. Edwin, our resident SQL guru, has a change to the script that he has suggested to Microsoft that would prevent this from occuring. We’re expecting that Microsoft will issue a blog post with an updated script addressing the issue. Meanwhile, if you have run it once you should not need to run it again.

    • Declan

      Just got home for the weekend and found my SBS2008 with this issue.

      I renamed and restored the two files from a backup that was from a week or so before the database hit the limit, and ran the script.

      Here’s hoping!

    • Declan

      Here’s what I did to solve this problem.

      First, I restored the SBSMONITORING database files from a backup taken a week or so before they hit the 4GB limit.

      I detached the database and moved the files to a disk with lots of free space, and then ran the script.

      My LDF file grew to over 45GB in size, and when the script finished it was still 45GB.

      I shrank both the database and the logs from Management studio and now the Database is 9 MB and the log is 500K.

      The whole thing too several hours, maybe 4 – 5 all told.

      Hope this helps.


    • Uli Maui Tech Guru

      I’m baffled that Microsoft still wants us to implement on premise server and then sell this junk .. all my clients servers are doing this again and again regardless of applying this fix .. the SBS servers should have some automated tasks pre-configured that could keep the server alive … SBS 2000, 2003, 2008, and perhaps now 2011 all need much more maintenance/management than what the sales fliers promises … .. this specific issue where monitoring crashes is outrageous as monitoring is what we need to keep an eye on server; and it always needs to be mended and fixed over time .. I’m getting really tired of this as I’ve got quite a few SBS servers to manage.

    • Pingback: sbs2008 compact sbsmonitoring sql db | Ian Navran – Tech Blog

    • dave M

      For one of my clients, I got this error in November 2011. I used the MS fix above where I ran the script where I took the file from MS’s skydrive folder.

      Despite the fact that this was a “fix” – I got this error again in July 2012 on the same server. I had to run the script again. In July 2011, this script took 1 hour and 10 minutes to run.

      The first time I ran this script in November 2011, my SBSMonitoring.LDF file grew to 30 GB. In July 2011, the SBSMonitoring.LDF file grew to 28 GB.

      Here are my steps for reducing the size of the SBSMonitoring.LDF file (which you should):

      1. Open an elevated SQL Server Management Studio Express
      2. Connect to the [servername]\SBSMonitoring database using Windows Authentication
      3. Expand Databases and right-click on SBS Monitoring
      4. Choose Tasks, then Backup
      5. Make a backup of the current database
      6. After backing up, right click SBSMonitoring and choose Tasks, Shrink -> file and Shrink -> database.
      7. In the opening window, choose Log in the File Type drop down list and click OK.
      8. Now the SBSMonitoring_log.ldf file should shrink.

    • Jaken Gardner

      I have been trying to get this to work for a while, The script is having no luck for me. The database just stays the same size

    • Thorsten

      To be honest: The solution that worked for me best in the last years was to always install MSSQL Server Management Studio Express and run the “shrink…” command on the respective database (SBSMonitoring).

      The skript caused more trouble than it solved my problems. Instead of smaller DBs I -very often- saw an increases in the database and the logfile. So stick to MS SQL SMSE (I love abbreviations ;-) ).
      Anyhow, many thanks for pointing this one out to us!

    • Michael Gossett

      Last thing missing is to re-attach the database…Honestly not sure why no one has posted that yet. I thought maybe it was withheld for some specific reason but it definitely doesn’t work without re-attaching the DB, which seems obvious but as I said I figured it didn’t say to do that for some specific reason.

      • Simon

        You want to shrink the log file of the SBSMonitoring
        Follow these step : (it’s woks for every SQL DATABASE)
        1.Open an elevated SQL Server Management Studio Express
        2. Connect to the [servername]\SBSMonitoring database using Windows Authentication
        3. Expand Databases and right-click on SBS Monitoring
        4 .Right click select properties
        5. Go top page OPTIONS
        6. Change Recovery model to BULK-LOG and click OK
        7. Shrink the LOG FILE
        8. Change backup the Recovery model to FULL and click OK