SBS Monitoring 4GB (2008) Now what?

Bobby Janow

unread,

Feb 23, 2010, 11:51:06 AM2/23/10

to

How do I either delete the monitoring database or move it? I have SQL
Express, can see the database, have shrunk the log database, but stuck with
the .mdf SBS Monitoring. Errors all over the place saying I can add or
modify the data.

Help and TIA

Bobby

Cliff Galiher - MVP

unread,

Feb 23, 2010, 2:04:42 PM2/23/10

to

I'd start 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

And make sure to read through the comments of this blog post too. They
cover manually running the cleanup the first time (which I recommend) since,
at 4 gigs, it'll take awhile.

-Cliff

"Bobby Janow" <bja...@msn.com> wrote in message
news:DAB484F2-D0A4-4614...@microsoft.com...

Larry Struckmeyer[SBS-MVP]

unread,

Feb 23, 2010, 2:19:25 PM2/23/10

to

And, just to add to Cliff's reply, you should be seeing events in the logs
similiar to the following:

Log Name: Application
Source: MSSQL$SBSMONITORING
Date: 12/16/2009 4:15:47 PM
Event ID: 1827
Task Category: (2)
Level: Error
Keywords: Classic
User: SYSTEM
Computer: xxx.xxx.com
Description:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative
database size would exceed your licensed limit of 4096 MB per database.

Log Name: Application
Source: MSSQL$SBSMONITORING
Date: 12/16/2009 4:15:47 PM
Event ID: 1105
Task Category: (2)
Level: Error
Keywords: Classic
User: SYSTEM
Computer: xxx.xxx.com
Description:
Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database
'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space
by deleting unneeded files, dropping objects in the filegroup, adding additional
files to the filegroup, or setting autogrowth on for existing files in the
filegroup.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

Bobby Janow

unread,

Feb 23, 2010, 3:28:27 PM2/23/10

to

Running it now. I had cleaned up the log database but it's growing now with
this command running. Hope this does it, thanks. I'll post back.

"Cliff Galiher - MVP" <cgal...@gmail.com> wrote in message
news:4BC054AB-20AC-4ADE...@microsoft.com...

Bobby Janow

unread,

Feb 23, 2010, 3:28:47 PM2/23/10

to

Yes I am.

"Larry Struckmeyer[SBS-MVP]" <lstruc...@mis-wizards.com> wrote in message
news:4e683515b23d8...@news.microsoft.com...

Bobby Janow

unread,

Feb 23, 2010, 4:05:49 PM2/23/10

to

My log file is over 6 GB now. Yikes, do I stop it?

"Cliff Galiher - MVP" <cgal...@gmail.com> wrote in message
news:4BC054AB-20AC-4ADE...@microsoft.com...

Bobby Janow

unread,

Feb 23, 2010, 4:13:24 PM2/23/10

to

The command ate up all my HD space and now my database is corrupt. Any
ideas? Can I move it to another partition?

"Cliff Galiher - MVP" <cgal...@gmail.com> wrote in message
news:4BC054AB-20AC-4ADE...@microsoft.com...

Bobby Janow

unread,

Feb 23, 2010, 4:17:56 PM2/23/10

to

Even better, is there a way to wipe out the whole thing and start all over?

"Cliff Galiher - MVP" <cgal...@gmail.com> wrote in message
news:4BC054AB-20AC-4ADE...@microsoft.com...

Susan Bradley

unread,

Feb 23, 2010, 4:45:52 PM2/23/10

to

No, you run the risk of major mangling

SBS Monitoring Database issue collection.
=====================================

As you may already know that monitoring on SBS 2008 uses SQL express
edition instance. In SQL express edition, there is a limitation of a
database size which is 4GB. So what is going to happen if monitoring
database is full? It will not be able to log anything and generate SQL
errors like two ones below:

To solve this issue, we need to run the SQL script in the article below.

SBS 2008 Console May Take Too Long to Display Alerts and Security
Statuses, Display Not Available, or Crash
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

The SQL script adds a feature to cleanup database every 30 days. This
can reduce the size of database. Running this SQL script will
immediately trigger the cleanup task.

Issue: Insufficient space to run above script
-----------------------------------------------

As a matter of fact, the above script will require lots of disk space on
this monitoring database to execute, usually goes to 20 GB and will be
shrink if this script is running successfully. By default, monitoring
database is located in C drive, so if the customer doesn't have enough
free space on C drive. They will have a big problem, exchange database
will be down, DNS, DHCP problem, ï¿½ due to inefficient free space, also
the SQL script will fail possibly

If in this case, we need to shrink the monitoring database manually.
There are two files associate to monitoring database, they are

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

The huge file should be SBSMonitoring_log.ldf

To shrink it:

1. Go to Start, type SQL server management studio express, when it shows
in the search result, right click it and choose run as administrator

NOTE: You must run SQL management studio as administrator to do the
steps below.

2. Accept default configuration, it should show Monitoring by default,
click Connect.
3. Expand Databases and find the SBSMonitoring, right click it and
choose Tasks -> Back up, create a full backup.
4. After backing up, right click SBSMonitoring and choose Tasks, Shrink,
file.
5. In the opening window, choose Log in the File Type drop down list and
click OK.
6. Now the SBSMonitoring_log.ldf file should shrink.

The issue should be resolved now.

Issue: Database is detached
----------------------------

For above issues, you may have some pre-troubleshooting work before you
contact Microsoft Professional. There are chances you manually detach
the monitoring database to have a try. Unfortunately, this will cause
some problem. The database will not be attached again as the limitation
is 4096 MB. You cannot reinstall the monitoring components because
reinstalling monitoring will NOT recreate the database. To solve this
issue, we need to recover the database by replacing it with a database
file from clean and good working machine. You could post your issue in
forum and Microsoft support professional will diagnosis the problem and
even send database files from good clean machine and get it attach to
the monitoring instance. Fortunately, monitoring database doesn't rely
on domain name and server name. Steps are:

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. Get the good database file from clean installed machine received from
your support professional 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 tuned by the SQL command.

5. Start the SQL Server(SBSMONITORING) service

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

For monitoring report doesn't generated issue
-------------------------------------------------------

Usually, it could be caused by corrupted monitoring component or missing
or corrupted monitoring databases.

For corrupted monitoring component, we can follow the repair guide.

Repair monitoring and reporting features in Windows Small Business
Server 2008
http://technet.microsoft.com/en-us/library/dd560614(WS.10).aspx

For monitoring database, we need to first check whether the following
two files are existing.

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

For monitoring email cannot be sent issue.
------------------------------------------------
Most of this issue is caused by incorrect configuration on Exchange
receive connectors. You can all Exchange Receive connectors and run fix
my network wizard to fix SBS specific ones. However, for Default
<servername> connector, FNCW cannot create it, you need to manually
create by steps in the article below:

Fix My Network wizard (FNCW) Fails to Fix the Exchange SMTP Connectors
in SBS 2008
http://blogs.technet.com/sbs/archive/2008/09/29/fix-my-network-wizard-fncw-fails-to-fix-the-exchange-smtp-connectors-in-sbs-2008.aspx

If this doesn't work, we could investigate this issue by reviewing the
following log "C:\Program Files\Windows Small Business
Server\Logs\MonitoringServiceLogs" folder, there will be a file called
DataServiceComponents.log, this file usually is very huge, this is
because it contains almost every detailed error file in it. However, it
is incredibly useful, you will be able to find the exact error. Here is
an example which shows why reporting email failed to send.

[364] 091128.120849.1072: AdminTME: Status: TaskId =
TaskGenerateMonitoringReport, RootTaskId = TaskGenerateAndEmailReport,
Success: True, Warning: False, Continue: True, Message:

---<< This line shows the TaskGenerateAndEmailReport, email generating
and sending message can be found here, you need to search this key word
to locate the root cause.

[364] 091128.120850.4192: Exception:
---------------------------------------
An exception of type 'Type: System.Net.Mail.SmtpException, System,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has
occurred.
Timestamp: 11/28/2009 12:08:50
Message: Failure sending mail.
Stack: at System.Net.Mail.SmtpClient.Send(MailMessage message)
at
Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.MailUtil.SendMail(String
from, String[] to, String subject, Boolean isBodyHTML, String body,
String inlineAttachmentPath, String[] attachments)
---------------------------------------
An exception of type 'Type: System.Net.WebException, System,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has
occurred.
Timestamp: 11/28/2009 12:08:50
Message: Unable to connect to the remote server
Stack: at System.Net.ServicePoint.GetConnection(PooledStream
PooledStream, Object owner, Boolean async, IPAddress& address, Socket&
abortSocket, Socket& abortSocket6, Int32 timeout)
at System.Net.PooledStream.Activate(Object
owningObject, Boolean async, Int32 timeout, GeneralAsyncDelegate
asyncCallback)
at System.Net.PooledStream.Activate(Object
owningObject, GeneralAsyncDelegate asyncCallback)
at System.Net.ConnectionPool.GetConnection(Object
owningObject, GeneralAsyncDelegate asyncCallback, Int32 creationTimeout)
at
System.Net.Mail.SmtpConnection.GetConnection(String host, Int32 port)
at System.Net.Mail.SmtpClient.Send(MailMessage message)
---------------------------------------
An exception of type 'Type: System.Net.Sockets.SocketException, System,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has
occurred.
Timestamp: 11/28/2009 12:08:50
Message: No connection could be made because the target machine actively
refused it fe80::309c:a749:6920:e429%10:25
Stack: at System.Net.Sockets.Socket.DoConnect(EndPoint
endPointSnapshot, SocketAddress socketAddress)
at
System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure,
Socket s4, Socket s6, Socket& socket, IPAddress& address,
ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout,
Exception& exception)

The key aspect of the exception is this:
No connection could be made because the target machine actively refused
it fe80::309c:a749:6920:e429%10:25

(In this case, the IP address is an IPv6 address which is not correct,
it should be an IPv4 address for SBS 2008, we solved the issue by
removing IPv6 address in host files.)

[TIP]: If you have any tough issue about monitoring, go to C:\Program
Files\Windows Small Business
Server\Logs\MonitoringServiceLogs\DataServiceComponents.log

bobby

unread,

Feb 23, 2010, 8:28:20 PM2/23/10

to

I read all this stuff at least 5 times but it doesn't tell me what to do. I
have shrunk the log file successfully but what do I do with the
sbsmonitoring.mdf which is over 4GB? Is there no way to reduce that other
than the script which expands my log file beyond belief? Something is not
right here or I'm totally missing something.

"Susan Bradley" <sbra...@pacbell.net> wrote in message
news:u$sWTGNtK...@TK2MSFTNGP06.phx.gbl...

Cliff Galiher - MVP

unread,

Feb 23, 2010, 8:42:17 PM2/23/10

to

No, you misunderstood. The growth to the log file is TEMPORARY:

>> As a matter of fact, the above script will require lots of disk space on
>> this monitoring database to execute, usually goes to 20 GB and will be
>> shrink if this script is running successfully.

Note the "and will [be] shrink if this script is running successfully.

But yes, you need space on your C drive to run the script that will
ultimately, eventually, shrink your data file. With that said, if you don't
have 20 gigs of free space, you probably need to think about provisioning
more space for that partition anyways. NTFS operates best with 25% free
space for journalling, temporary work, etc. Using that logic, a partition
size of 80 gigs would want 20 gigs free, and I'd never run an SBS system on
only an 80 gig partition. 120? Sure, but then you want 30 gigs free.....

So you'll want to see your space used, do some calculating, and add space as
needed. Sure, shrinking the SQL data file *will* help, but if your
allocated space is that tight, it won't help enough....

-Cliff

"bobby" <bja...@msn.com> wrote in message
news:2CCAB43F-AB87-4CA1...@microsoft.com...

Gregg Hill

unread,

Feb 23, 2010, 10:50:10 PM2/23/10

to

Cliff,

Regarding the free space needed by NTFS, is that a hard rule that it needs
25% free, no matter the size of the partition?

I'm having a hard time seeing that a 500GB partition needs to keep 125GB
free space, regardless of whether or not the actual data is even near the
other 75% of the drive. I thought NTFS was more efficient than that!

Gregg Hill

--
Gregg's pet peeves:

First of all, what does a peeve look like, and why would anyone want one as
a pet?

Peeve #1: Apostrophes: when in doubt, leave them out! You will be correct
more often than not.

Its = Belonging to it. For example, "Look at the sky. Its color is blue."
It's = It is. For example, "It's hot today."
It's = It has. For example, "It's been nice talking to you."
Its' = completely incorrect usage. Stop it!

Peeve #2: Your vs. You're
"Your" means belonging to you, as in, "It's your truck."
"You're" means "You are." Example, you're probably about ready to throttle
me for this peeve!

"Cliff Galiher - MVP" <cgal...@gmail.com> wrote in message

news:F140A0E5-C789-4765...@microsoft.com...

bobby

unread,

Feb 23, 2010, 10:59:31 PM2/23/10

to

OIC, thanks for that info. So I cleaned up a bit of the log files and other
junk and now have about 40 GB free on a 100 GB partition. Do I run the
script and if so, how long will it take? It said to manually clean up first
(or something like that) if the database was 4 GB. How do I manually clean
it before the full script? It's not so much the size of the database that's
bothering me but all the errors I'm getting. Heck, I'd even let the script
run overnight if I wasn't worried about crashing the server due to lack of C
drive space. Do you know how this happened all of a sudden?

"Cliff Galiher - MVP" <cgal...@gmail.com> wrote in message

news:F140A0E5-C789-4765...@microsoft.com...

Bobby Janow

unread,

Feb 24, 2010, 9:02:00 AM2/24/10

to

Major mangling is not really an issue if it doesn't work at all. So there is
no way to wipe the database and start over? Doesn't that seem like a severe
limitation to the OS?

"Susan Bradley" <sbra...@pacbell.net> wrote in message
news:u$sWTGNtK...@TK2MSFTNGP06.phx.gbl...

Bobby Janow

unread,

Feb 24, 2010, 11:15:49 AM2/24/10

to

(1 rows affected)
Msg 1913, Level 16, State 1, Server COMPANY\SBSMONITORING, Line 4
The operation failed because an index or statistics with name
'_SBS_BLOG_index_W
MICollectedData_5_K4_K1_K5_2' already exists on table
'dbo.WMICollectedData'.

Cliff Galiher - MVP

unread,

Feb 24, 2010, 3:14:26 PM2/24/10

to

How long it'll take is very dependent on how much data is there, how much
can be cleaned up, and the I/O performance of your machine. There is no
easy way to determine that ahead of time. Just set some time aside, run the
script, and *MONITOR* your available disk space. And, as always, have a
backup.

-Cliff

"bobby" <bja...@msn.com> wrote in message

news:1C7DD272-3EF6-4844...@microsoft.com...

Susan Bradley

unread,

Feb 24, 2010, 7:40:51 PM2/24/10

to

Hmmm hang loose that script should have worked just fine.

Red Scourge

unread,

Aug 5, 2010, 6:55:46 PM8/5/10

to

I had this same problem. MDF file was 1.2GB, LDF file was 200MB, memory usage was about 1.2GB!!! I believe the problem stems from the fact that the WMI system collects far too many logs on many people's systems, SQL Server Express seems to stupidly try to to load the database mostly into RAM despite the fact that it's hardly ever read from and thus read performance doesn't matter. To make things worse, it does not even attempt to clear anywhere near a sufficient amount of old records, which also leads to massive disk usage, even in a network with as few as 10 users!

After reading all about this I decided to try something similar, but I did things a little differently, and found the result to be WAY more effective.

The basic summary of what I did was make a full backup of the data files just incase, then delete most of the rows from the dbo.WMICollectedData table in SQL Management Studio Express, THEN shrunk the database, then the files, then the database again, then on a hunch after examining the actual data stored in the above mentioned database table, I decreased the size of the system's logs in Event Viewer and set them to archive and keep instead of overwrite.

Steps:

Open SQL Server Management Studio Express > Connect to SBSMonitoring > Expand to Tables, dbo.WMICollectedData, right click, Properties (saw it was a MASSIVE table), I then realized WMI collected data is not critical to keep, and decided to delete the data.

I deleted all but the last 200,000 rows or so (DELETE FROM dbo.WMICollectedData WHERE ID < (SELECT MAX(ID)-200000 FROM dbo.WMICollectedData), then went to do the shrink as others have suggested:

Right-click on SBSMonitoring > Tasks > Shrink > Database > OK, repeat for Tasks > Shrink > Files, then repeat for Tasks > Shrink > Database yet again.

After shrinking that database way down, I tweaked Event Viewer a bit:

Start Menu > Right click Computer > Manage > Expand Diagnostics > Event Viewer > Windows Logs, then for each of the logs in there except Forwarded Events, right click the log > Properties > for "Maximum log size ( KB ):" Enter "10240", and for "When maximum even log size is reached:" choose "Archive the log when full, do not overwrite events".

My hope in the last part is that the WMI Collected Events sounds like it's probably tied in with the Event Viewer somehow. If my instincts are correct, by not only limiting the max size more strictly and making it archive the old data instead of having huge limits and overwriting, perhaps it will help to keep the SBSMONITORING database nice and small (perhaps). Perhaps I am wrong and there is no correlation between the two, but in that case, if there's a way to add some sort of routine into the SBSMonitor database that attempts to limit the number of records in the WMICollectedData table, or perhaps export them somewhere for use by some log viewing program like Event Viewer, that would most likely alleviate this absolutely horrible issue.

Susan Bradley wrote:

Bobby Janow wrote:Hmmm hang loose that script should have worked just fine.
24-Feb-10