You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

The Windows SBS Console of Windows Small Business Server 2008 may crash, display "Not Available" for Other Alerts, or require a long time to display the Security and Other Alerts statuses

SYMPTOMS
The Windows SBS Console of Windows Small Business Server 2008 may show one or more of the following symptoms.

Symptom 1

The Not Available status appears in Other Alerts under the Network Essentials Summary section of the Home page.

Symptom 2

An exception that resembles the following is logged in the Console.log file:
[5164] 090620.094247.9164: Exception: --------------------------------------- An exception of type 'Type: System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has occurred. Timestamp: 06/20/2009 09:42:47 Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)                    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)                    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)                    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()                    at System.Data.SqlClient.SqlDataReader.get_MetaData()                    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)                    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)                    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)                    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)                    at System.Data.SqlClient.SqlCommand.ExecuteScalar()                    at Microsoft.WindowsServerSolutions.SystemHealth.Monitoring.MonitoringSQLDataStore.GetAlertCountPerType(ComputerType type) 

Symptom 3

After a long time, the statuses appear for Security and Other Alerts under the Network Essentials Summary section of the Home page.

Symptom 4

After a long time, the values appear in the Security Status column and in the Other Alerts column on the Computers tab of the Network page.

Symptom 5

When you try to manually generate a report on the Reports tab, the Windows SBS Console may crash if the Security check box is selected to add security content to the report.

CAUSE
This issue occurs because the Monitoring database is very large. The Windows SBS Console cannot query the Monitoring database quickly.
RESOLUTION
To resolve this issue, run the following Windows PowerShell script. This script reduces the historical data in the Monitoring database by reducing the historical data that is kept from 90 days to 30 days. Also, this script creates indexes that can be used to run the queries quicker.

To run this script, follow these steps:
  1. Click Start, type Notepad in the Search box, and then click Notepad in the search result.
  2. Copy and paste the following script into Notepad:
    cls;############################################################################ SQL Scripts#########################################################################$sqlScript = @"USE [SBSMonitoring]BEGIN TRAN T1UPDATE [SBSMonitoring].[dbo].[Settings] SET [Value] = 30 WHERE [Name] = 'CleanupPeriod'COMMIT TRAN T1SELECT N'Set CleanupPeriod to 30 days'EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]SELECT N'CleanupDatabase job done.'-- Create IndexesIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2')	DROP INDEX [_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2] ON [dbo].[WMICollectedData] WITH ( ONLINE = OFF )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]SELECT N'Succeeded to create index _SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Reports]') AND name = N'_SBS_BLOG_index_Reports_5_K2_K3_1_4')	DROP INDEX [_SBS_BLOG_index_Reports_5_K2_K3_1_4] ON [dbo].[Reports] WITH ( ONLINE = OFF )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]SELECT N'Succeeded to create index _SBS_BLOG_index_Reports_5_K2_K3_1_4'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_index_WMICollectedData_5_K2D_K4_K5')	DROP INDEX [_SBS_BLOG_index_WMICollectedData_5_K2D_K4_K5] ON [dbo].[WMICollectedData] WITH ( ONLINE = OFF )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]SELECT N'Succeeded to create index _SBS_BLOG_index_WMICollectedData_5_K2D_K4_K5'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Alerts]') AND name = N'_SBS_BLOG_index_Alerts_8_7_')	DROP INDEX [_SBS_BLOG_index_Alerts_8_7_] ON [dbo].[Alerts] WITH ( ONLINE = OFF )CREATE INDEX [_SBS_BLOG_index_Alerts_8_7_] ON [SBSMonitoring].[dbo].[Alerts] ([DefinitionID], [ComputerID]) INCLUDE ([DateOccured])SELECT N'Succeeded to create index _SBS_BLOG_index_Alerts_8_7_'-- Create StatisticsIF EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_stat_WMICollectedData_5_1_4')	DROP STATISTICS [dbo].[WMICollectedData].[_SBS_BLOG_stat_WMICollectedData_5_1_4]CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_5_1_4] ON [dbo].[WMICollectedData]([WMIInstanceID], [ID], [WMIPropertyID])SELECT N'Succeeded to create statistics _SBS_BLOG_stat_WMICollectedData_5_1_4'IF EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_dta_stat_WMICollectedData_4_1_5_2_3')	DROP STATISTICS [dbo].[WMICollectedData].[_SBS_BLOG_dta_stat_WMICollectedData_4_1_5_2_3]CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_4_1_5_2_3] ON [dbo].[WMICollectedData]([WMIPropertyID], [ID], [WMIInstanceID], [DateCollected], [StatusID])SELECT N'Succeeded to create statistics _SBS_BLOG_dta_stat_WMICollectedData_4_1_5_2_3'IF EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_dta_stat_WMICollectedData_5_1_3_4')	DROP STATISTICS [dbo].[WMICollectedData].[_SBS_BLOG_dta_stat_WMICollectedData_5_1_3_4]CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_5_1_3_4] ON [dbo].[WMICollectedData]([WMIInstanceID], [ID], [StatusID], [WMIPropertyID])SELECT N'Succeeded to create statistics _SBS_BLOG_dta_stat_WMICollectedData_5_1_3_4'IF EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_dta_stat_WMICollectedData_5_4_2')	DROP STATISTICS [dbo].[WMICollectedData].[_SBS_BLOG_dta_stat_WMICollectedData_5_4_2]CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_5_4_2] ON [dbo].[WMICollectedData]([WMIInstanceID], [WMIPropertyID], [DateCollected])SELECT N'Succeeded to create statistics _SBS_BLOG_dta_stat_WMICollectedData_5_4_2'IF EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_stat_WMICollectedData_2_1_5')	DROP STATISTICS [dbo].[WMICollectedData].[_SBS_BLOG_stat_WMICollectedData_2_1_5]CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_2_1_5] ON [dbo].[WMICollectedData]([DateCollected], [ID], [WMIInstanceID])SELECT N'Succeeded to create statistics _SBS_BLOG_stat_WMICollectedData_2_1_5'IF EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[dbo].[WMICollectedData]') AND name = N'_SBS_BLOG_stat_WMICollectedData_1_3_5_2')	DROP STATISTICS [dbo].[WMICollectedData].[_SBS_BLOG_stat_WMICollectedData_1_3_5_2]CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_1_3_5_2] ON [dbo].[WMICollectedData]([ID], [StatusID], [WMIInstanceID], [DateCollected])SELECT N'Succeeded to create statistics _SBS_BLOG_stat_WMICollectedData_1_3_5_2'"@;$userInput = Read-Host -Prompt @"Before running this script, follow the instructions in the Knowledge Base article 981939 to back up your database files.  If you are ready to run the script, type the letter 'Y' to confirm that you have backed up the database, and then press 'Enter'. "@;if([System.String]::Compare($userInput, "Y", $true) -ne 0){	exit;}################################################################################## Save the sql file to temp folder###############################################################################$sqlFile = [System.IO.Path]::Combine($Env:TEMP, "UpdateSBSMonitoring.sql");$sqlScript | Out-File -FilePath "$sqlFile" -Force;################################################################################## Get SBS2008 log folder###############################################################################$regKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WSSG";$regVal = "ProductLogDir";$sqlLogPath =[System.Environment]::ExpandEnvironmentVariables([Microsoft.Win32.Registry]::GetValue($regKey, $regVal, $null));if([System.String]::IsNullOrEmpty($sqlLogPath)){	$sqlLogPath = $Env:TEMP;}$dateTime = Get-Date;$sqlLogFile = "UpdateSBSMonitoring_{1}_{0:HH}{0:mm}{0:ss}.log" -f $dateTime, $dateTime.ToShortDateString().Replace('/', '_');$sqlLogFile = [System.IO.Path]::Combine($sqlLogPath, $sqlLogFile);################################################################################## Call " SqlCmd " to execute the sql script###############################################################################$cmdPara = " -S $Env:COMPUTERNAME\SBSMonitoring -E -i `"$sqlFile`"";Write-Host ("Sqlcmd" + $cmdPara);$processStartInfo = New-Object System.Diagnostics.ProcessStartInfo("Sqlcmd", $cmdPara);$processStartInfo.UseShellExecute = $false;$processStartInfo.ErrorDialog = $true;$processStartInfo.CreateNoWindow = $true;$processStartInfo.RedirectStandardOutput = $true;$processStartInfo.RedirectStandardError = $true;$process = [System.Diagnostics.Process]::Start($processStartInfo);$startTime = [System.DateTime]::Now;$process.WaitForExit(); $finishTime = [System.DateTime]::Now;$stdOutput = $process.StandardOutput.ReadToEnd();$errOutput = $process.StandardError.ReadToEnd();if($process.ExitCode -eq 0 -and $errOutput.Length -eq 0){	Write-Host $stdOutput;	Write-Host "The script ran successfully."}else{	Write-Host $stdOutput;	Write-Host $errOutput;	Write-Host "An error occurred while running the script. For details about this error, see the log file at $sqlLogFile.";}[System.String]::Join([System.Environment]::NewLine, `	(("Sqlcmd" + $cmdPara), `	("Started at: " + $startTime), `	("Finished at:" + $finishTime), `	"Standard Output: ", $stdOutput, `	"Error Output: ", $errOutput))| Out-File $sqlLogFile -Force;
  3. Save the file by using the following file name:
    KB981939.ps1.

    Note We recommend that you save the file to a location that you can easily access. For example, save the file to the C:\windows\temp folder.
  4. Back up the Monitoring database files. To do this, follow these steps:
    1. Click Start, type Services.msc in the Search box, right-click services.msc in the search result, and then click Run as administrator.
      UAC If you are prompted for an administrator password or for confirmation, type the password, or provide confirmation.
    2. Locate the SQL Server (SBSMONITORING) service.
    3. Right-click the SQL Server (SBSMONITORING) service, and then click Stop.
    4. Back up the files in the following folder:
      C:\Program Files (x86)\Microsot SQL Server\MSSQL.1\MSSQL\Data
    5. Right-click the SQL Server (SBSMONITORING) service, and then click Start.
  5. Click Start, type PowerShell in the Search box, right-click Windows PowerShell Modules in the search result, and then click Run as administrator.
    UAC If you are prompted for an administrator password or for confirmation, type the password, or provide confirmation.
  6. In the Windows PowerShell Modules window, change the current directory to the location where you saved the KB981939.ps1 file.
  7. Type the following command to run the Windows PowerShell script, and then press ENTER:
    .\kb981939.ps1
    Note You must configure the Windows PowerShell execution policy to allow scripts to run before you run this command. Otherwise, you receive the following error message when you run the command:
    File C:\KB981939.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.

    For more information about how to configure the Windows PowerShell execution policy, visit the following Microsoft TechNet website:
  8. Type Y when you receive the following message:
    Before running this script, follow the instructions in the Knowledge Base article 981939t to back up your database files.
    If you are ready to run the script, type the letter 'Y' to confirm that you have backed up the database, and then press 'Enter'.

    Note If the Monitoring database is very large, the script may run for 10 minutes or for a longer time.

    If the script does not run successfully, you must restore the backup of the Monitoring database files.
REFERENCES
For more information about this issue, visit the following TechNet website:
Properties

Article ID: 981939 - Last Review: 06/29/2010 06:13:00 - Revision: 6.0

  • Windows Small Business Server 2008 Premium
  • Windows Small Business Server 2008 Standard
  • kbinfo kbhowto kbsurveynew kbtshoot kbexpertiseadvanced KB981939
Feedback