This morning a new client contacted me explaining that they were having problems connecting to the Internet and no Emails were arriving… could I drop by to take a look?

Normally, when my customers use Microsoft Small Business Server I’m able to remotely access their server without having to travel but in this instance, with the Internet Connectivity problem it was a case of travelling to client site.

On arriving I set to work establishing the cause of the problem – using the normal tools of ping and tracert.

Tracert showed that there were packets getting dropped out in the Internet and so my first instinct was that there was a core router problem on the ISP’s network which would explain things nicely.  To make sure, I tried to logon to my client’s SBS 2008 server to make sure there were no critical problems there such as a failed DNS service.

Oddly, despite prompting for login credentials I was unable to login to the server remotely.  That didn’t seem right and so I went to the server and logged in on a real life keyboard…  Login was fine and so I checked the event logs to see if there was anything major afoot.

In the Application Event Log I saw the following error:

Log Name: Application
Source: MSSQL$SBSMONITORING
Date: 23/10/2015 12:19:08
Event ID: 1827
Task Category: (2)
Level: Error
Keywords: Classic
User: SYSTEM
Computer: ***********************
Description:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Now, Microsoft Small Business Server 2008 has a limit on the size of any SQL Server database that you use on SBS.  The limit is 4Gb after which, you can’t write to it. As SBS uses SQL Server to log all the monitoring data, once the database hits 4GB SBS2008 is unable to log to it which means the server starts to choke… Helpful eh?

HOW TO FIX SBSMonitoring Database that is Full

 

The first port of call normally is to try Shrinking the database using SQL Server Management Studio. I attempted this and it freed up a massive 4Mb – meaning that the database was still way above the 4Gb limit.

It was time for plan B.  

 

In the SBSMonitoring database is a stored procedure that “cleans the database” – removing old log entries that you no longer need.

To clear log entries that are older than 30 days this is a handy script that clears the log after 30 days and does some housekeeping.


USE SBSMonitoring
UPDATE [SBSMonitoring].[dbo].[Settings] SET [Value] = 30 WHERE [Name] = 'CleanupPeriod'
EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]
CREATE NONCLUSTERED INDEX [_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2] ON [dbo].[WMICollectedData] ([WMIPropertyID] ASC,[ID] ASC, [WMIInstanceID] ASC) INCLUDE ( [DateCollected]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [_SBS_BLOG_index_Reports_5_K2_K3_1_4] ON [dbo].[Reports] ([ConfigurationID] ASC, [DateGenerated] ASC) INCLUDE ([ID], [Data]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [_SBS_BLOG_index_WMICollectedData_5_K2D_K4_K5] ON [dbo].[WMICollectedData] ([DateCollected] DESC, [WMIPropertyID] ASC, [WMIInstanceID] ASC) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE INDEX [_SBS_BLOG_index_Alerts_8_7_] ON [SBSMonitoring].[dbo].[Alerts] ([DefinitionID], [ComputerID]) INCLUDE ([DateOccured])
CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_5_1_4] ON [dbo].[WMICollectedData]([WMIInstanceID], [ID], [WMIPropertyID])
CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_4_1_5_2_3] ON [dbo].[WMICollectedData]([WMIPropertyID], [ID], [WMIInstanceID], [DateCollected], [StatusID])
CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_5_1_3_4] ON [dbo].[WMICollectedData]([WMIInstanceID], [ID], [StatusID], [WMIPropertyID])
CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_5_4_2] ON [dbo].[WMICollectedData]([WMIInstanceID], [WMIPropertyID], [DateCollected])
CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_2_1_5] ON [dbo].[WMICollectedData]([DateCollected], [ID], [WMIInstanceID])
CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_1_3_5_2] ON [dbo].[WMICollectedData]([ID], [StatusID], [WMIInstanceID], [DateCollected])

Sadly, on running the stored procedure a critical error was reported that an Index was corrupt and to run DBCC.

Not wanting to corrupt the data further and risk loss I decided that a database rebuild was the best action. Even though it was only log data, it’s best to grab a backup and then start afresh.

Next, Plan C

 

This powershell script renames the existing SBSMonitoring database and recreates a brand new and empty database.  Before running it, I made a backup of the existing SBSMonitoring database just in case.

When you run a powershell script, SBS checks if the script is Securely Signed by default – Powershell will throw an error when you run the script as it’s not securely signed. To bypass this check type the following in Powershell before running the script:


Set-ExecutionPolicy Unrestricted

The script contents are:


# This sample script recreates the SBSMonitoring Database in SBS 2008 or SBS 2011 Standard.
# Creates a new SBSMonitoring Database (renames an existing one if present).
# Creates the 2 default Reports
# Computer population will take place within 30 minutes as per the DataCollectorSvc schedule
# In SBS 2011 std it requires Framework v4 Assemblies - Launch MoveDataPowerShellHost.exe as an admin from SBS BIN folder, then run this PS1
#
# Check http://blogs.technet.com/b/sbs for more information
#
# This sample script is provided AS IS without warranty of any kind.
# Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose.
# The entire risk arising out of the use or performance of the sample scripts and documentation remains with you.
# In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

#Create SQL scripts
get-content “C:\Program Files\Windows Small Business Server\data\Monitoring\DatabaseSetup.sql” |
ForEach-Object {$_ -replace “\{0}”,”SBSMonitoring”} |
set-content c:\windows\temp\DatabaseSetup-new.sql

get-content “C:\Program Files\Windows Small Business Server\data\Monitoring\Tables.sql” |
ForEach-Object {$_ -replace “\{0}”,”SBSMonitoring”} |
set-content c:\windows\temp\Tables-new.sql

get-content “C:\Program Files\Windows Small Business Server\data\Monitoring\StoredProcedures.sql” |
ForEach-Object {$_ -replace “\{0}”,”SBSMonitoring”} |
set-content c:\windows\temp\StoredProcedures-new.sql

Stop-Service DataCollectorSvc
Stop-Service ‘MSSQL$SBSMONITORING’

$date=Get-Date -format ‘yyyy_MM_dd_hh_mm_ss’

#Find path for SBSMonitoring installation
if ((gwmi win32_operatingsystem).version -like “6.0*”) {
#SBS2008 : HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\SBSMONITORING\Setup\SQLPath
$key=get-itemproperty “HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\SBSMONITORING\Setup\”
} else
{
#SBS2011 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SBSMONITORING\Setup\SQLPath
$key=get-itemproperty “HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SBSMONITORING\Setup\”
}

$dbpath=$key.SQLpath+”\Data\SBSMonitoring.mdf”
$ldpath=$key.SQLpath+”\Data\SBSMonitoring_log.LDF”
if (test-path $dbpath) {
ren $dbpath -NewName “SBSMonitoring.mdf.$date”
Write-Host Backup file created – remove it after functionality is restored to recover the disk space “$dpath.$date”
}
if (test-path $ldpath) {
ren $ldpath -NewName “SBSMonitoring_log.LDF.$date”
Write-Host Backup file created – remove it after functionality is restored to recover the disk space “$ldpath.$date”
}
Start-Service ‘MSSQL$SBSMONITORING’
sqlcmd -E -S $env:COMPUTERNAME\SBSMonitoring -i c:\windows\temp\DatabaseSetup-new.sql | Out-Null
sqlcmd -E -S $env:COMPUTERNAME\SBSMonitoring -i c:\windows\temp\Tables-new.sql | Out-Null
sqlcmd -E -S $env:COMPUTERNAME\SBSMonitoring -i c:\windows\temp\StoredProcedures-new.sql | Out-Null

Start-Service DataCollectorSvc
[system.reflection.assembly]::LoadFrom(“c:\program files\Windows small business server\bin\MonitoringCommon.dll”)

## Summary Report
$reportContent = new-object Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContent($null)
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::Summary)
$sch1 = new-object Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.Scheduler(3,15)

$summaryReport = new-object Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportConfiguration([Guid]“86949FAC-C719-42AE-A8C2-0C43CBE64927″,”Summary Network Report”,”This report includes summary information about the performance of your network.”,$true,$reportContent,”",$sch1)

$MailEnabledDistributionListQuery = “(&(objectCategory=group)(groupType:1.2.840.113556.1.4.803:=8)(!groupType:1.2.840.113556.1.4.803:=2147483648)(mail=*)(sAMAccountName=Windows SBS Administrators))”
$reportDomainRecipients = New-Object Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.UserCollection($null)
$reportDomainRecipients = [Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ADUtil]::GetUsers($MailEnabledDistributionListQuery)

$summaryReport.DomainEmailAddresses = $reportDomainRecipients

$out = “INSERT INTO ReportConfiguration (ID, Name, Description, Enabled, Content, DomainUserEmails, ExternalUserEmails, Schedule) VALUES(`’”+$summaryReport.Id+”`’,`’”+$summaryReport.Name+”`’,`’”+$summaryReport.Description+”`’,`’”+$true+”`’,`’”+$summaryReport.Content+”`’,`’”+$summaryReport.DomainEmailAddressesXML+”`’,`’”+$summaryReport.ExternalEmailAddresses+”`’,`’”+$summaryReport.ScheduleXML+”`’)”
“use SBSMonitoring” | Out-File -FilePath c:\windows\Temp\TempQuery.sql
$out | Out-File -FilePath c:\windows\Temp\TempQuery.sql -Append
## Full Report
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::Security)
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::Updates)
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::Backup)
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::OtherAlerts)
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::EmailUsage)
$reportContent.Add([Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportContentType]::EventLog)

$sch2 = new-object Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.Scheduler(3,15,”Sunday”)
$fullReport = new-object Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.ReportConfiguration([Guid]“13B8397F-B197-4E5B-B3D0-340FCB5E216A”,”Detailed Network Report”,”This report includes detailed information about the performance of your network.”,$true,$reportContent,”",$sch2)
$fullReport.DomainEmailAddresses = $reportDomainRecipients

$out = “INSERT INTO ReportConfiguration (ID, Name, Description, Enabled, Content, DomainUserEmails, ExternalUserEmails, Schedule) VALUES(`’”+$fullReport.Id+”`’,`’”+$fullReport.Name+”`’,`’”+$fullReport.Description+”`’,`’”+$true+”`’,`’”+$fullReport.Content+”`’,`’”+$fullReport.DomainEmailAddressesXML+”`’,`’”+$fullReport.ExternalEmailAddresses+”`’,`’”+$fullReport.ScheduleXML+”`’)”
$out | Out-File -FilePath c:\windows\Temp\TempQuery.sql -Append

##Create
sqlcmd -E -S $env:COMPUTERNAME\SBSMonitoring -i c:\windows\temp\TempQuery.sql

After this script is run, you can check the database has been created in SQL Server Management Studio - it should show it to be about 2mb in size.