Purpose :

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

Script :
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
Monitor the Software registry hive size + send report by email

Leave a Reply

Your email address will not be published. Required fields are marked *