Excel archives - The Multifunctioning DBA

Jun 25 2009 9:24PM GMT
PowerShell SQL Server Health Check Script

Posted by: Colin Smith
Powershell, SQL Server, MSSQL, Excel, Monitoring
OK so I think that I have everything I want in my script to check on the health of my SQL Servers. I will run this at least once a day and more if needed. I hope that this will help some people out and if you have any questions, comments, or ideas of what else might be good to include please let me know.
SQL Health Check Script:



#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
#$Excel.visible = $False

#$workbook = $excel.Workbooks.Open("D:\pac health checks.xlsx")

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 2

#Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv "\\pni-vmdbasql\d$\monitoring\inputs\sqltab.txt"
$servers = Import-Csv "\\ent-pocpacapcx01\d$\monitoring\sqltab.txt"

#########################################################
foreach ($entry in $servers)
{
$torp = $entry.TorP
$mon = $entry.monitor
$machine = $entry.server
$errorlog = $entry.errorlog
$os = $entry.os2000
$iname = $entry.Instance
if ($iname -eq "Null")
{
$instance = "$machine"
}
else
{
$instance = "$machine\$iname"
}
if ($torp -eq "Prod")
{
$ServerType = "Production"
}
else
{
$ServerType = "Test"
}
$instance = $instance.toupper()

#########################################################
#foreach ($instance in get-content "D:\SQL_Servers.txt")
#{

#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
################################################

#This script gets SQL Server database information using PowerShell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

#$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

#Formatting using Excel

$version = $s.VersionString

if ($version -like "*9.00.1399*")
{        $sqlversion = "SQL Server 2005 No Service Pack"
}
elseif ($version -like "*9.00.2047*")
{        $sqlversion = "SQL Server 2005 SP1"
}
elseif ($version -like "*9.00.3042*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3068*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3054*")
{        $sqlversion = "SQL Server 2005 SP2 with Q934458"
}
elseif ($version -like "*9.00.3073*")
{        $sqlversion = "SQL Server 2005 SP2 with Q954606"
}
elseif ($version -like "*9.00.3186*")
{        $sqlversion = "SQL Server 2005 SP2 with Q939562"
}
elseif ($version -like "*9.00.4035*")
{        $sqlversion = "SQL Server 2005 SP3"
}
## SQL Server 2000 Checks
elseif ($version -like "*8.00.194*")
{        $sqlversion = "SQL Server 2000 No SP"
}
elseif ($version -like "*8.00.384*")
{        $sqlversion = "SQL Server 2000 SP1"
}
elseif ($version -like "*8.00.532*")
{        $sqlversion = "SQL Server 2000 SP2"
}
elseif ($version -like "*8.00.760*")
{        $sqlversion = "SQL Server 2000 SP3"
}
elseif ($version -like "*8.00.818*")
{        $sqlversion = "SQL Server 2000 SP3 with MS03-031"
}
elseif ($version -like "*8.00.2039*")
{        $sqlversion = "SQL Server 2000 SP4"
}
elseif ($version -like "*8.00.2040*")
{        $sqlversion = "SQL Server 2000 NoSP with Q274329"
}
elseif ($version -like "*8.00.2050*")
{        $sqlversion = "SQL Server 2000 SP4 with MS08-040"
}
elseif ($version -like "*8.00.2187*")
{        $sqlversion = "SQL Server 2000 SP1 with Q923849"
}
elseif ($version -like "*8.00.2282*")
{        $sqlversion = "SQL Server 2000 QFE"
}
elseif ($version -like "*8.00.3073*")
{        $sqlversion = "SQL Server 2000 SP2 with Q954606"
}
#SQL Server 7
elseif ($version -like "*7.00.1094*")
{        $sqlversion = "SQL Server 7 SP4 with Q815495, Q821279"
}
#SQL Server 2008
elseif ($version -like "*10.0.1442.32*")
{        $sqlversion = "SQL Server 2008 X64 MSDN Beta"
}

else
{        $sqlversion = "Check me $version"
}

$Sheet.Cells.Item($intRow,3) = "Version:"
$Sheet.Cells.Item($intRow,4) = $sqlversion
$Sheet.Cells.Item($intRow,5) = $ServerType
if ($Servertype -eq "Production")
{
$fgColor = 35
}
else
{
$fgColor = 0
}
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

###########################################################

$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True
$Sheet.Cells.Item($intRow,4).Font.Bold = $True
$Sheet.Cells.Item($intRow,5).Font.Bold = $True

##############################################
## Are full and tranlog backups running?
## DB Reindexing running?
##############################################

$intRow ++

$jobsserver = $s.JobServer
$jobs = $jobsserver.Jobs
$ijob = 0
$backjob = 0
$tranjob = 0
foreach ($job in $jobs)
{

if (($job.name -like "*backup*") -and ($job.name -notlike "*Tranlog*"))
{
$backjob = 1
$fullbackup = $job.name
$fullbackupstatus = $job.LastRunOutcome
$fullbackupdate = $job.LastRunDate

}
if ($job.Name -like "*tranlog*")
{
$tranjob = 1
$tranlogbackup = $job.name
$tranlogbackupstatus = $job.LastRunOutcome
$tranlogbackupdate = $job.LastRunDate

}
if ($job.Name -like "*index*")
{
$ijob = 1
$rebuildindex = $job.Name
$rebuildindexstatus = $job.LastRunOutcome
$rebuildindexdate = $job.LastRunDate

}
}

## Check to verify full backups are running ##
$dayago = [datetime]::Now.AddDays(-1)
if (($fullbackupstatus -eq "Succeeded") -and ($fullbackupdate -gt $dayago))
{
$fullbackupjob = "Full Backups Running"
}
elseif(($fullbackupstatus -ne "succeeded") -or ($fullbackupdate -lt $dayago))
{
$fullbackupjob = "Full Backups Failing"
}
if ($backjob -eq 0)
{
$fullbackupjob = "No Full Backup Job"
}

## Check to verify that Tranlogs backups are running fine ##
$hourago = [datetime]::Now.AddHours(-1)
if (($tranlogbackupstatus -eq "Succeeded") -and ($tranlogbackupdate -gt $hourago))
{
$tranbackupjob = "Tran Backup Running"

}
if(($tranlogbackupstatus -ne "succeeded") -or ($tranlogbackupdate -lt $hourago))
{
$tranbackupjob = "Tran BackupFailing"
}
if ($tranjob -eq 0)
{
$tranbackupjob = "No Tran Backup Job"
}

$weekago = [datetime]::Now.AddDays(-7)
if (($rebuildindexstatus -eq "Succeeded") -and ($rebuildindexdate -gt $weekago))
{
$indexjob = "Indexing Running"

}
elseif(($rebuildindexstatus -ne "succeeded") -or ($rebuildindexdate -lt $weekago))
{
$indexjob = "Indexing Failing"

}
if ($ijob -eq 0)
{
$indexjob = "No Indexing Job"

}

$Sheet.Cells.Item($intRow,1) = $fullbackupjob
$Sheet.Cells.Item($intRow,2) = $tranbackupjob
$Sheet.Cells.Item($intRow,3) = $indexjob
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True

##############################################

$intRow++

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,3) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,4) = "SPACE AVAILABLE (MB)"
$Sheet.Cells.Item($intRow,5) = "DATA DRIVE"
$Sheet.Cells.Item($intRow,6) = "SPACE AVAILABLE ON DISK (GB)"
$Sheet.Cells.Item($intRow,7) = "MIRROR STATUS"
$Sheet.Cells.Item($intRow,8) = "LOG SIZE (MB)"

#Format the column headers
for ($col = 1; $col –le 8; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

$intRow++
#######################################################
#      #This script gets SQL Server database information using PowerShell
#
#      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#
#      # Create an SMO connection to the instance
#      $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
#
#      $dbs = $s.Databases
#
#      #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
#
#      #Formatting using Excel

foreach ($db in $dbs)
{
$name = $db.name
$model = $db.recoverymodel
if ($model -eq 1)
{
$modelname = "Full"
}
elseif ($model -eq 2)
{
$modelname = "Bulk Logged"
}
elseif ($model -eq 3)
{
$modelname = "Simple"
}

$logfiles = $db.LogFiles
foreach ($log in $logfiles)
{
$logsize = $log.size/1KB
$logsize = [math]::Round($logsize, 2)
}

#if(($name -ne "master") -and ($name -ne "model")) # -and ($name -ne "msdb"))
#{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB

#Format the results to a number with three decimal places
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

$Sheet.Cells.Item($intRow, 1) = $db.Name

$Sheet.Cells.Item($intRow, 2) = $modelname
$Sheet.Cells.Item($intRow, 3) = "{0:N3}" -f $db.Size

#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -eq 0.00)
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow, 4) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

$dblocation = $db.primaryfilepath

$dblocation = $dblocation.split(":")

$dbdrive = $dblocation[0]
$drives = Get-WmiObject -ComputerName $machine Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
foreach($drive in $drives)
{
$size1 = $drive.size / 1GB
$size = "{0:N2}" -f $size1
$free1 = $drive.freespace / 1GB
$free = "{0:N2}" -f $free1
$ID = $drive.DeviceID
$a = $free1 / $size1 * 100
$b = "{0:N2}" -f $a

if ($dbdrive -eq "C")
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow,5) = $dbdrive
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

if ($id -like "$dbdrive*")
{

if ($free1 -lt 5)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
if (($ID -eq "C:") -and ($free1 -lt 1))
{
$fgColor = 38
}
$Sheet.Cells.Item($intRow,6) = $free1
$Sheet.Cells.item($intRow, 6).Interior.ColorIndex = $fgColor
}
}
if($version -like "*2000*")
{$mirrorstate = 0}
else
{
$mirrorstate = $db.MirroringStatus
}
if ($mirrorstate -eq 0)
{
$mirror = "No Mirror"
}
if ($mirrorstate -eq 1)
{$mirror = "Suspended"
}
if($mirrorstate -eq 5)
{
$mirror = "Synchronized"
}
if ($mirrorstate -eq 1)
{
$fgcolor = 38
}
else
{
$fgcolor = 0
}
$Sheet.Cells.Item($intRow,7) = $mirror
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

if ($logsize -gt 500)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow,8) = $logsize
$Sheet.Cells.item($intRow, 8).Interior.ColorIndex = $fgColor

$intRow ++

#}
}

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

$workbook.saveas("D:\pac health checks.xlsx")
#$excel.Quit()
$WorkBook = $Null
$WorkSheet = $Null
$Excel = $Null
[GC]::Collect()
Here is a sample of what the report will look like when you have run it.