How to resolve SBS 2008 Monitoring Database is full to capacity

Small Business Server 2008

A customer recently had an issue where their SBS 2008 Monitoring Database was full, and had stopped generating and emailing SBS reports.

In this post I’ll look at shrinking the database, recreating it and and moving it from the default location to an alternative drive with more free space.

Looking in the application event log, ID 1105 and 1827 were being logged.

Application Event Log ID 1105 ID 1827The detailed events are:

Event ID 1827

Event ID 1105

After a little research I first tried the instructions in the Official SBS Blog post here.

I’ve used this command before successfully on databases that aren’t at maximum capacity, you’ll need plenty of diskspace to run it, so if your database is on C: make sure you have enough. In this particular instance the script failed to work, but it increased my database log file from 1.6GB in size to 28GB in size (see the image below)!

The instructions and listed below for convenience (with some additional screen shots).

Download the following file to the server you are going to be working on:

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:windowstemp.

Complete a backup of the SBSMonitoring Database

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.

Proceed to run the following set of commands to implement the improvements

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 updateSBSMonitoring.sqlNote: you can see from the above the original database size is 4GB and log file 1.6GB.

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)

Unfortunately this didn’t work for me in this instance, and the database log file expanded to 28GB!

Very large sbsmonitoring_log.ldf fileAnd the error i received was:

Msg 1913 Level16 State 1I then moved on to my fall back plan to recreate the SBS Monitoring database. For this I followed the instructions in the Official SBS Blog post here.

Again the instructions and listed below for convenience (with some additional screen shots).

Download the following package:
https://skydrive.live.com/self.aspx/.Public/MonitoringDBRecreate/Create-SBSMonitoringDb.zip?cid=d5fe25afb6c3615f&sc=documents

Extract the files to c:\windows\temp

Launch an administrative PowerShell session

Launch the script:

C:windowstempCreate-SBSMonitoringDb.ps1

Creat-SBSMonitoringDB.ps1
After completing the script and verifying that that monitoring is working, complete the steps in this article: 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

SBSMonitoring database in original locationNow the database is recreated we can run the updatesbsmonitoring.sql command.

Sqlcmd updatesbsmonitoring.sqlThis time it finished successfully.

Now, as the database can grow to 4GB and the log file to 1.6GB I want to move them from C: to E: where i have more space.

Login to SQL Server Management Studio Express, use windows authentication and connect to servernameSBSMonitoring.

SQL Server Manager Connect to SBSMonitoringExpand Databases and Right-click the SBSMonitoring database and select Tasks – Detach.

SBSMonitoring Database Tasks DetachTick the option to drop open connections, and click ok.

Copy the database files from C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLData to E:SBSMonitoringDB

In SQL Server Management Studio Express, Right-click the Database, and select Tasks – Attach.

SBSMonitoring Database Tasks AttachIn the Attach Databases dialogue box, click Add, browse to E:SBSMonitoringDBSBSMonitoring.mdf, click OK, Click OK to close the dialogue box.

SBSMonitoring Database on E:The newly created tiny SBS Monitoring database is now on E:

It will take a while for all the machines in your network to report their status, the Official SBS Blog notes it can take up to 48 hours. In practice I’ve noticed the database size increase and been able to run a summary report after a couple of hours.

References

1. 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

2. http://blogs.technet.com/b/sbs/archive/2011/08/22/how-to-recreate-the-sbsmonitoring-database.aspx

3. http://www.thirdtier.net/2010/03/sbs-2008-monitoring-database-fills-to-capacity/

4. http://kwsupport.com/2013/05/

Related Articles:

1. Connect to Computer missing on SBS 2008 Remote Web Workplace

2. How to delete the Dataservicecomponents.log file in SBS 2008

3. How to install Exchange 2010 SP3 on SBS 2011

One thought on “How to resolve SBS 2008 Monitoring Database is full to capacity

  1. Matt Aston

    The Database instance wants a backslash in there

    servernameSBSMonitoring

    servername\SBSMonitoring

    might be usefull to someone ..

    Reply

Leave a Reply

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