如果SBS2008的监视数据库历史数据保存时间设的比较长,可能会造成监视数据的邮件的延迟或者无法发送。(默认的保存时间为90天。)由于在SBS的控制台没有直接的图形界面用以改变这个设置,我们需要使用PowerShell更改这一设定。
 
具体做法如下:
 
1.复制以下脚本至写字板:
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;
2.保存为 database.ps1
3.PowerShell中运行这一脚本即可更改时间设定。