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
Source: MSSQL$SBSMONITORING
Date: 3/19/2010 7:36:29 AM
Event ID: 1827
Task Category: (2)
Level: Error
Keywords: Classic
User: SYSTEM
Computer: SBS2008.domain.local
Description:
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.
- http://cid-d5fe25afb6c3615f.skydrive.live.com/self.aspx/.Public/updateSBSMonitoring.sql
- We recommend you save the file to an easy to access path, such as c:\windows\temp.
- Open Services from Administrative Tools, Services
- Accept the UAC prompt
- Find and stop the SQL Server (SBSMONITORING) service.
- Make a copy of the files in the following folder:
- C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data
- Once the file backup is completed, start the SQL Server (SBSMONITORING) service.
- Open an Administrative Command Prompt (Run As Admin)
- 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):
- Sqlcmd -S %computername%\SBSMonitoring -E -i c:\windows\temp\updateSBSMonitoring.sql
- 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:
- Open an elevated SQL Server Management Studio Express
- Connect to the <servername>\SBSMonitoring database using Windows Authentication
- Expand Databases and right-click on SBS Monitoring
- Choose Tasks, then Backup
- Make a backup of the current database
- Next Choose Tasks, then Detach to detach the database from SQL
- Open Services.msc and stop the SQL Server (SBSMonitoring) and SQL Full Text Search (SBSMonitoring) services
- Open an elevated Windows Explorer and drill down to c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data and replace the SBSMonitoring and SBSMonitoring_Log files with the blank ones you obtained.
- Start the services that you stopped earlier
- 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. 
Get Support 
Blog 
Twitter 
Facebook 
LinkedIN
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.
Get Support Blog Twitter Facebook LinkedIN
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’.
Kevin, I’m going to bet that you had already run that script. The original script comes from here: http://blogs.technet.com/sbs/archive/2009/07/14/sbs-2008-console-may-take-too-long-to-display-alerts-and-security-statuses-display-not-available-or-crash.aspx It’s a July post and I’ll bet you already ran that after I posted the blog post the last time.
Kevin,
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.
Amy,
Perhaps this a dumb question but how do I get blank SBSMonitoring and SBSMonitoring_Log files?
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!
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.
Declan
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.
[...] I found this really useful post on how to reduce the size of the sbsmonitoring sql db on sbs2008. Our file was around 3.5gb, along with a 3gb log file I downloaded a .sql script to update the logging to 30 days. I used this post, but thought i’d copy it to this blog incase the other disapears. souce : SBS 2008 Monitoring Database Fills to Capacity [...]