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

Article translations Article translations
Article ID: 981939 - View products that this article applies to.
Expand all | Collapse all

On This Page

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 T1
    UPDATE [SBSMonitoring].[dbo].[Settings] SET [Value] = 30 WHERE [Name] = 'CleanupPeriod'
    COMMIT TRAN T1
    SELECT N'Set CleanupPeriod to 30 days'
    EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]
    SELECT N'CleanupDatabase job done.'
    
    -- Create Indexes
    IF 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 Statistics
    IF 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.
      Collapse this imageExpand this image
      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.
    Collapse this imageExpand this image
    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:
    How to configure the Windows PowerShell execution policy
  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:
SBS 2008 Console May Take Too Long to Display Alerts and Security Statuses, Display Not Available, or Crash

Properties

Article ID: 981939 - Last Review: June 29, 2010 - Revision: 6.0
APPLIES TO
  • Windows Small Business Server 2008 Premium
  • Windows Small Business Server 2008 Standard
Keywords: 
kbinfo kbhowto kbsurveynew kbtshoot kbexpertiseadvanced KB981939

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com