The purpose of this script is to send a daily report with the following informations :
– hostname
– software registry hive size
This script has been written to get a status of our SQL server with the problem described here (http://rusanu.com/2013/02/15/registry-bloat-after-sql-server-2012-sp1-installation/) :
SQL Server 2012 installation has the potential to leave an msiexec.exe installer process running after the installation finishes, as described in Windows Installer starts repeatedly after you install SQL Server 2012 SP1
But the this problem has a much sinister side effect: it causes growth of the HKLM\Software registry hive. Except for the System hive, all the other registry hives are still restricted in size to a max of 2GB, see Registry Storage Space:
Views of the registry files are mapped in paged pool memory…The maximum size of a registry hive is 2 GB, except for the system hive.
Pre-requesite :
Create a text file containing the hostname of the servers you want to check:
Example of “sql_upd.txt” :
server1
server2
server3
Function send_mail([string]$message,[string]$subject) {
$emailFrom = "from@mail.com"
$emailTo = "to1@mail.com" , "to2@mail.com"
$emailCC = "cc@mail.com"
$smtpServer = "smtp_server.mail.com"
Send-MailMessage -SmtpServer $smtpServer -To $emailTo -Cc $emailCC -From $emailFrom -Subject $subject -Body $message -BodyAsHtml
}
$date = Get-Date -Format "yyyy-MM-dd"
$subject = "SQL Server Software registry hive size Status : "+$date
$message = @()
$message = "<HTML><HEAD>
<style type='text/css'>
table.tftable {font-family:verdana,arial,sans-serif;font-size:12px;color:#333333;border-width: 1px;border-color: #729ea5;border-collapse: collapse;}
table.tftable th {font-family:verdana,arial,sans-serif;font-size:12px;background-color:#acc8cc;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;text-align:left;}
table.tftable tr {font-family:verdana,arial,sans-serif;background-color:#d4e3e5;}
table.tftable td {font-family:verdana,arial,sans-serif;font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;}
</style>
</HEAD><body>"
$message += "<table class='tftable' border='1'><tr><th><p>Hostname</p></th><th><p>Ping</p></th><th><p>software registry hive file</p></th></tr>"
gc sql_upd.txt | % {
$MachineName = $_
$PingStatus = Gwmi Win32_PingStatus -Filter "Address = '$MachineName'" | Select-Object StatusCode
If ($PingStatus.StatusCode -eq 0){
$size = Get-Item \\$_\c$\windows\system32\config\software | select length
$size_MB = ($size.Length/1024)/1024
if ($size_MB -lt 90){
$message += "<tr><td><p><font color=green>" + $MachineName + "</p></td><td><p>OK</p></td>"
$message += "<td><p><font color=green><b>" + $size_MB + " MB</b></font></p></td></tr>"
}
elseif (($size_MB -ge 90) -and ($size_MB -lt 110)){
$message += "<tr><td><p><font color=orange>" + $MachineName + "</p></td><td><p>OK</p></td>"
$message += "<td><p><font color=orange><b>" + $size_MB + " MB</b></font></p></td></tr>"
}
else {
$message += "<tr><td><p><font color=red>" + $MachineName + "</p></td><td><p>OK</p></td>"
$message += "<td><p><font color=red><b>" + $size_MB + " MB</b></font></p></td></tr>"
}
}
Else {
$message += "<tr><td><p><font color=red>" + $MachineName + "</p></td><td><p>KO</p></td><td><p>NA</p></td></tr>"
}
}
send_mail $message $subject