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

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;