rasimmers/Get-DatabaseFileSpace.ps1

## Get-DatabaseFileSpace.ps1
function Get-DatabaseFileSpace {
    param (
        [string]$Instance = "LocalHost"
    )
    begin {
        $svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Instance
    } #begin
    process {
        $dbfl = foreach ($db in $svr.Databases) {
            $dbname = $db.Name
            foreach ($fg in $db.FileGroups) {
                foreach ($fl in $fg.Files) {
                    $dirnm = $fl.FileName | Split-Path -Parent
                    $filnm = $fl.FileName | Split-Path -Leaf

                    $fl | select Name,
                                 @{Name="DBName"; Expression={$dbname}},
                                 @{Name="Directory"; Expression={$dirnm}},
                                 @{Name="FileName"; Expression={$filnm}},
                                 @{Name="Size(MB)";Expression={$fl.Size/1024}},
                                 @{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}},
                                 @{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}} ,
                                 @{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
                                 @{Name="Free%";Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
                } #foreach ($fl in $fg.Files)
            } #foreach ($fg in $db.FileGroups)

            foreach ($fl in $db.LogFiles) {
                $dirnm = $fl.FileName | Split-Path -Parent
                $filnm = $fl.FileName | Split-Path -Leaf
                $fl | select Name,
                             @{Name="DBName"; Expression={$dbname}},
                             @{Name="Directory"; Expression={$dirnm}},
                             @{Name="FileName"; Expression={$filnm}},
                             @{Name="Size(MB)";Expression={$fl.Size/1024}},
                             @{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}},
                             @{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}},
                             @{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
                             @{Name="Free%";Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
            } #foreach ($fl in $db.LogFiles)
        } #foreach ($db in $svr.Databases)
    } #process
    end{$dbfl} #end
} #Get-DatabaseFileSpace

$instances = "Server1", "Server2", "Server3"
$instanceReportHTML = foreach ($instance in $instances) {
    #Get the data from the instance
    $dbfl = Get-DatabaseFileSpace -Instance $instance
    #Convert the data to a HTML fragment and send it to $instanceReportHTML
    $dbfl | ConvertTo-HTML -Title ("Report for {0}" -f $instance) -Fragment

}

# Template for HTML
$html = @"
<html>
    <head>
        <style>
            BODY{
                background-color:white;
            }

            TABLE{
                font-family: Arial;
                font-size: 12px;
                width:100%;
                height:75%;
                border-width: 1px;
                border-style: solid;
                border-color: black;
                border-collapse: collapse;
            }

            TH{
                border-width: 1px;
                padding: 0px;
                border-style: solid;
                border-color: black;
                color:white;
                background-color: green;
            }

            TD{
                border-width: 1px;
                padding: 0px;
                border-style: solid;
                border-color: black;
                background-color:white
            }
        </style>
    </head>
    <body>
        <h1>Database File Space Report</h1>

        $instanceReportHTML
    </body>
</html>
"@

$body = $instanceReportHTML | Out-String
$EmailFrom = "xxxxx@gmail.com"
$EmailTo = "aaa@gmail.com,bbb@gmail.com"
$Subject = "Datafile size report"
$SMTPServer = "smtp.gmail.com"
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
$SMTPClient.EnableSsl = $true
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("xxxxx@gmail.com", "pwd123");
$message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $body)
$message.IsBodyHtml = $true;
$SMTPClient.Send($message)
	function Get-DatabaseFileSpace {
	param (
	[string]$Instance = "LocalHost"
	)
	begin {
	$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Instance
	} #begin
	process {
	$dbfl = foreach ($db in $svr.Databases) {
	$dbname = $db.Name
	foreach ($fg in $db.FileGroups) {
	foreach ($fl in $fg.Files) {
	$dirnm = $fl.FileName \| Split-Path -Parent
	$filnm = $fl.FileName \| Split-Path -Leaf

	$fl \| select Name,
	@{Name="DBName"; Expression={$dbname}},
	@{Name="Directory"; Expression={$dirnm}},
	@{Name="FileName"; Expression={$filnm}},
	@{Name="Size(MB)";Expression={$fl.Size/1024}},
	@{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}},
	@{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}} ,
	@{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
	@{Name="Free%";Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
	} #foreach ($fl in $fg.Files)
	} #foreach ($fg in $db.FileGroups)

	foreach ($fl in $db.LogFiles) {
	$dirnm = $fl.FileName \| Split-Path -Parent
	$filnm = $fl.FileName \| Split-Path -Leaf
	$fl \| select Name,
	@{Name="DBName"; Expression={$dbname}},
	@{Name="Directory"; Expression={$dirnm}},
	@{Name="FileName"; Expression={$filnm}},
	@{Name="Size(MB)";Expression={$fl.Size/1024}},
	@{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}},
	@{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}},
	@{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
	@{Name="Free%";Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
	} #foreach ($fl in $db.LogFiles)
	} #foreach ($db in $svr.Databases)
	} #process
	end{$dbfl} #end
	} #Get-DatabaseFileSpace

	$instances = "Server1", "Server2", "Server3"
	$instanceReportHTML = foreach ($instance in $instances) {
	#Get the data from the instance
	$dbfl = Get-DatabaseFileSpace -Instance $instance
	#Convert the data to a HTML fragment and send it to $instanceReportHTML
	$dbfl \| ConvertTo-HTML -Title ("Report for {0}" -f $instance) -Fragment

	}

	# Template for HTML
	$html = @"
	<html>
	<head>
	<style>
	BODY{
	background-color:white;
	}

	TABLE{
	font-family: Arial;
	font-size: 12px;
	width:100%;
	height:75%;
	border-width: 1px;
	border-style: solid;
	border-color: black;
	border-collapse: collapse;
	}

	TH{
	border-width: 1px;
	padding: 0px;
	border-style: solid;
	border-color: black;
	color:white;
	background-color: green;
	}

	TD{
	border-width: 1px;
	padding: 0px;
	border-style: solid;
	border-color: black;
	background-color:white
	}
	</style>
	</head>
	<body>
	<h1>Database File Space Report</h1>

	$instanceReportHTML
	</body>
	</html>
	"@

	$body = $instanceReportHTML \| Out-String
	$EmailFrom = "xxxxx@gmail.com"
	$EmailTo = "aaa@gmail.com,bbb@gmail.com"
	$Subject = "Datafile size report"
	$SMTPServer = "smtp.gmail.com"
	$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
	$SMTPClient.EnableSsl = $true
	$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("xxxxx@gmail.com", "pwd123");
	$message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $body)
	$message.IsBodyHtml = $true;
	$SMTPClient.Send($message)