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)
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.
- We recommend you save the file to an easy to access path, such as c:windowstemp.
- 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 ServerMSSQL.1MSSQLData
- 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:windowstempupdateSBSMonitoring.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 ServerMSSQL.1MSSQLData 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?