使用powershell 监控sqlserver 一些状态

 

需要监控基本的数据库参数，如恢复模型，页面验证，自动关闭，自动收缩，数据库所有者，自动创建统计启用，数据库创建日期等。因为是windows系统需要接住poweshell。

我们的要求是，所有数据库都应该处于完全恢复模式，并且应该被监控。

此外，我添加了其他重要参数，如AutoShrink, AutoClose等。

脚本效果输出如下:

#Change value of following variables as needed 
$ServerList = Get-Content "D:SCMSSQLServer.txt" 
$OutputFile = "D:SCMSSQLRecoverModel_MDA_$((Get-Date).ToString('ddMMyyyy_hhmm')).htm" 
$HTML = '<style type="text/css"> 
   table{font-family: Calibri,Candara,Segoe,Segoe UI,Optima,Arial,sans-serif;width:100%; border: black;  border-style: double;  border-width: 2px;}
   table td,table th{font-size:1em;border:1px solid #98bf21;padding:3px 7px 2px;} 
   table th{font-size:1.1em;padding-top:5px;padding-bottom:4px;background-color:#81BEF7;color:#fff} 
  
    </Style>' 

################################################################################# Author: Sandeep Charaya : http://ilearnsql.wordpress.com/# Date: 16.06.2020# Comment: Script to obtain the disk space on remote servers################################################################################

$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header> 
        <TR> 
    <TH align=center><B>Date</B></TH>
            <TH align=center><B>DatabaseName</B></TH> 
            <TH align=center><B>RecoveryModel</B></TH> 
            <TH align=center><B>PageVerify</B></TH> 
    <TH align=center><B>AutoClose</B></TH> 
    <TH align=center><B>AutoShrink</B></TH> 
    <TH align=center><B>DatabaseOwner</B></TH>
<TH align=center><B>AutoCreateStatisticsEnabled</B></TH>
    <TH align=center><B>DB CreateDate</B></TH>
        </TR>" 
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
ForEach ($ServerName in $ServerList) 
{ 
    $HTML += "<TR bgColor='#81F7D8'><TD colspan=9 align=center><B>$ServerName</B></TD></TR>" 
     
    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName  
    Foreach($Database in $SQLServer.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master" -and $_.Name -ne "ReportServerTempDB" -and $_.Name -ne "ReportServer$SGSI2W0256TempDB" -and $_.Name -ne "ReportServer$SGSI2W0256"}) 
    { 
$DatabaseName=$Database.Name
        $SimpleRecoveryModel=$Database.RecoveryModel
$PageVerify=$Database.pageverify
$AutoClose=$Database.autoclose
$Autoshrink=$Database.autoshrink
$DBOwner=$Database.owner
$AutoCreateStatisticsEnabled=$Database.AutoCreateStatisticsEnabled
$CreateDate=$Database.CreateDate
        $EndDate=Get-Date


IF ($SimpleRecoveryModel -eq 'simple')
{
$color1 = 'red'
}
ELSE {$color1 = 'Green'}
IF ($PageVerify -ne 'Checksum')
{
$color2 = 'red'
}
ELSE {$color2 = 'Green'}
IF ($AutoClose -eq 'True')
{
$color3 = 'red'
}
ELSE {$color3 = 'Green'}
IF ($Autoshrink -eq 'True')
{
$color4 = 'red'
}
ELSE {$color4 = 'Green'}
IF ($DBOwner -eq "" -Or $DBOwner -Like "*sadm*")
{
$color5 = 'yellow'
}
ELSE {$color5 = 'Green'}
IF ($AutoCreateStatisticsEnabled -eq 'True')
{
$color6 = 'green'
}
ELSE {$color6 = 'red'}

              
 IF ( $color1 -eq 'red' -or $color2 -eq 'red' -or $color3 -eq 'red' -or $color4 -eq 'red' -or $color5 -eq 'red' -or $color6 -eq 'red')
 { 
$HTML += "<TR> 
    <TD>$(get-date)</TD>
                    <TD>$($Database.Name)</TD> 
                    <TD BGCOLOR='$color1' ALIGN=CENTER>$($Database.RecoveryModel)</TD> 
    <TD BGCOLOR='$color2' ALIGN=CENTER>$($Database.pageverify)</TD>
    <TD BGCOLOR='$color3' ALIGN=CENTER>$($Database.autoclose)</TD>
    <TD BGCOLOR='$color4' ALIGN=CENTER>$($Database.autoshrink)</TD>
    <TD BGCOLOR='$color5' ALIGN=CENTER>$($Database.owner)</TD>
<TD BGCOLOR='$color6' ALIGN=CENTER>$($Database.AutoCreateStatisticsEnabled)</TD>
<TD BGCOLOR='green' ALIGN=CENTER>$($Database.createdate)</TD>
                </TR>" 

    } 

}
} 

$HTML += "</Table></BODY></HTML>" 
$HTML | Out-File $OutputFile

 

 

adm_d14_jxpp_wx_robot_group_task_send_msg_di

posted @ 2022-03-22 19:20  DB乐之者  阅读(252)  评论(0)  编辑  收藏  举报