Technical Article

Monitor basic Database parameters like Recovery Model, Page Verify, Auto Close, Auto Shrink, DB Owner, Auto Create Statistics Enabled, DB Create Date etc with PowerShell command.

,

I have created this script for one of our client, the requirement was all the databases should be in full recovery model and should be monitored.

Also, I added other important parameters like AutoShrink, AutoClose etc.

Output:

#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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Generate Role Member Reports using dbatools and the ImportExcel PowerShell modules

Being able to get information and save it on a Excel as a report can be a common and tedious task. What if we could join dbatools and ImportExcel PorwerShell modules to accomplish this in a easier and fast way? We will see how to export roles and its' member and save that info on a multi spreadsheet Excel file with handy tables ready to be explored.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

2020-06-30

6,522 reads

Technical Article

SQL Service account status with PowerShell

One of my client has the requirement to have SQL Service account running with domain account and should not be with local account. You may receive SSPI error after changing it to domain account. In order to fix that issue check my previous blog "How to register SPN for SQL service account" Output:

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

2020-06-17

1,040 reads

Technical Article

How to register SPN for SQL service account

Check if the SPN is already registered: setspn -l domain\xxxxx If not, run below commands: setspn -A MSSQLSvc/abc.xx.companyname.com:1433 domain\xxxxx setspn -A MSSQLSvc/abc.xx.companyname.com domain\xxxxx setspn -A MSSQLSvc/abc:1433 domain\xxxxx setspn -A MSSQLSvc/ abc domain\xxxxx   Verify again, setspn -l domain\xxxxx

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

2020-06-17 (first published: )

7,276 reads