The SBSMonitoring database is nearing maximum size on SBS 2008

Have you received this error? The SBSMonitoring database is nearing maximum size on SBS 2008.

I was getting this on a SBS 2008 server running the SBS BPA. From the error message, it’s pretty clear that the SBSMonitoring database is too large to work normally. There is a fix for this and it turns out the same SQL script that is used for an SBS problem where the Console displays too slowly is part of the solution. To shrink the database, Please follow these steps:

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

1. http://cid-d5fe25afb6c3615f.skydrive.live.com/self.aspx/.Public/updateSBSMonitoring.sql

2. I recommend you save the file to an easy to access path, such as c:\windows\temp.

Step 2: 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 Server\MSSQL.1\MSSQL\Data

5.Once the file backup is completed, start the SQL Server (SBSMONITORING) service.

Step 3: 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:\windows\temp\updateSBSMonitoring.sql

2.Success will show an output like this:

Changed database context to ‘SBSMonitoring’

(1 rows affected)

If your database is completely filled up, then you can replace it with a clean empty one, to install your new clean database, please follow these steps:

1. Run services.msc.

2. Stop the Datacollectorsvc service(Windows SBS Manger Service), SQL Server(SBSMONITORING) service (To be able to unlock monitoring database files)

3. Change the name of those 2 files in case you will use them in the future:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring.mdf

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring_log.ldf

to

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring-bak.mdf

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SBSMonitoring_log-bak.ldf

4. Download http://cid-6ca40dd0d4c9caa6.skydrive.live.com/self.aspx/.Public/sbsmonitoring.zip file.

5. Unzip the zip file and copy those two files to C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder

Note: you don’t need to do detach or attach database in SQL Management Studio. And that database has already been tuned by the SQL command above.

6. Start the SQL Server(SBSMONITORING) service

7. Start the Datacollectorsvc service(Windows SBS Manger Service)

For more information on the beginning part of this article, you can refer to this article:

SBS 2008 Console May Take Too Long to Display Alerts and Security Statuses, Display Not Available, or Crash.