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.
The detailed events are:
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):
Note: 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!
And the error i received was:
I 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
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
Now the database is recreated we can run the updatesbsmonitoring.sql command.
This 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.
Expand Databases and Right-click the SBSMonitoring database and select Tasks – Detach.
Tick 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.
In the Attach Databases dialogue box, click Add, browse to E:SBSMonitoringDBSBSMonitoring.mdf, click OK, Click OK to close the dialogue box.
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
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
The Database instance wants a backslash in there
servernameSBSMonitoring
servername\SBSMonitoring
might be usefull to someone ..