Content Database and Site Collection Report

About the report

The below script will generate a detailed report of Content Databases and Site Collections in your Farm, send it in an HTML email to the recipients specified.

The Content Database section reports on Size (GB), Database Status, current Site Collection count and Max Site Collection limit.

The Site Collection section reports on all Site Collections per Content Database. This could be useful when deciding which Site Collections should be moved to another Content Database

The Script

To use the script
– Copy the below script to your environment.
– Update the variables in the mail info section.
– Create a scheduled task to run the script monthly.

<#
# Monthly Content Database and Site Collection Database Report
# Sends and email with details of Content Databases (Size, Status, SC Count) Per Web Application
# Also appends report of each Site Collection, size and URL per Content Database
# @Author Dan Sanders | dan@sanders.nz
#>

#Get SharePoint Content database sizes
if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

# Mail Info 
$SMTPServer = "mail.domain.com"
$emailFrom = "noreply@domain.com"
$emailTo = "<recipent@domain.com>", "<recipent@domain.com>", "<recipent@domain.com>", "<recipent@domain.com>"
$subject = "Monthly Report on Content Databases and Site Collections"
# Setup Content Database Table
$CDBTable = New-Object System.Text.StringBuilder
$CDBTableHeader = "<table class='tbl'><tr><th class='left'>Content Database</th><th class='left'>Size (GB)</th><th class='left'>Database Status</th><th class='left'>Current SC Count</th><th class='left'>Max SC</th></tr>"
# Setup Site Collection Table
$SCTable = New-Object System.Text.StringBuilder
$SCTable.Append("<table class='tbl'><tr><th class='left'>Site Collection URL</th><th class='left'>Size (MB)</th></tr>")
#CSS Styles for the Tables
$css = "
<style type='text/css'>
.tbl  {border-collapse:collapse;border-spacing:0;border-color:#999;}
.tbl td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#999;color:#444;background-color:#F7FDFA;}
.tbl th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#999;color:#fff;background-color:#26ADE4;}
.tbl .right{text-align:right;vertical-align:top}
.tbl .left{text-align:left;vertical-align:top}
</style>"
# Initilise Totals
$totalCDBSize, $totalCDBCount, $totalSCCount = 0
# Begin email body 
# Add CSS Styles and email header text
$emailBody = "$css <h1>Monthly Report on Content Databases and Site Collections</h1><h2>Content Database Report</h2>"
# Get all Web Applications
$webApps = Get-SPwebApplication

# For each Web Application in the SharePoint Farm
foreach($webApp in $webApps)
{
    # Grab all Content Databases and sort by size 
    $ContentDatabases = $webApp.ContentDatabases | Sort-Object disksizerequired -desc

    # Increment the total Content DB count
    $totalCDBCount += $webApp.ContentDatabases.Count
    
    # Begin Content Database table per Web Application
    $CDBTable.Append("<h3>Content Databases for $($webApp.url)</h3> $CDBTableHeader")
    
    foreach($ContentDatabase in $ContentDatabases)
    {
        # Get CBD size by GB 
        $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
        $CDBName = $ContentDatabase.Name
        
        # Create CDB Table Row
        $CDBTable.Append("<tr><td class='left'>$CDBName</td><td class='right'>$ContentDatabaseSize</td><td class='left'>$($ContentDatabase.Status)</td><td class='right'>$($ContentDatabase.CurrentSiteCount)</td><td class='right'>$($ContentDatabase.MaximumSiteCount)</td></tr>")
        
        # Increment the Total DB Size
        $totalCDBSize = $totalCDBSize + $ContentDatabaseSize
        
        # Site Collection Table Row = CDB name.
        $SCTable.Append("<tr><td class='left' colspan='2'><strong>$CDBName</strong></td></tr>")
        # Site Collection REPORT for CDB
        # Get all Site Collections for CDB and sort by size.
        $SCs = Get-SPSite -Limit All -ContentDatabase $CDBName | Select url, @{label="SC Size";Expression={[math]::Round($_.usage.storage/1MB)}} | Sort-Object "SC Size" -Descending
        foreach($SC in $SCs)
        {
            # Create SC Table Row
            $SCTable.Append("<tr><td class='left'>$($SC.url)</td><td class='right'>$($sc.'SC Size') MB</td></tr>")
            # Increment the total SC count
            $totalSCCount += 1
        }

    }
        # End Content Database Table for that Web Application
        $CDBTable.Append("</table>")
	
}

# End Site Collection Table for all Web Applications
$SCTable.Append("</table>")

# Update email body
# Append Content Database table and totals.
# Append Site Collection table and totals.
$emailBody += $CDBTable.ToString() + "<br/>
<Strong>Total Content DB Count $totalCDBCount</strong><br/>
<Strong>Total Content DB Size $totalCDBSize GB</strong><br/>
<h2>Site Collection Report</h2>
 $($SCTable.ToString()) 
<Strong>Total Site Collection Count $totalSCCount </strong>"

# Send HTML Email with $emailBody
if(!($SMTPServer) -OR !($emailFrom) -OR !($emailTo))
{
    Write-Host "No e-mail being sent, if you do want to send an e-mail, please enter the values for the following variables: $SMTPServer, $emailFrom and $emailTo."
}
else
{
    Send-MailMessage -SmtpServer $SMTPServer -From $emailFrom -To $emailTo -Subject $subject -Body $emailBody -BodyAsHTML 
}

Thanks to this post ‘Content Database size report using PowerShell‘ that generates a simple Content Database report into a text file which formed the basic structure of the above.
The post also describes how to create a scheduled task for those that are unsure.