<

Exchange 2007 Mailbox Database size and WhiteSpace using Powershell

Published on
12,846 Points
6,046 Views
3 Endorsements
Last Modified:
Approved
Introduction:

Exchange 2007 Mailbox database is a .edb file which includes the individual mailbox size, whitespace and the database architecture/configuration information.

What is Whitespace?

Any email deleted, mailbox deleted or mailbox moved to other database does not retrieve that free space back to the disk. Instead, the pages used by the mailbox or email are freed up and left as empty space. Online maintenance that is run daily will consolidate this empty space and makes it available for reuse. This consolidated empty space is called whitespace and the size of available whitespace size can be determined by event ID 1221. This whitespace does not decrease the EDB file size but any new item added to the database will try to use these empty chunks of space thus preventing EDB file growth until this whitespace is used up.

Script:

Assuming that a dedicated drive is used for database, the used disk space will be equal to the sum of EDB file size and the Catalog folder size. Catalog folder holds the index files for the .edb file provided Indexing is enabled for the database.

This article provides a powershell script to report the Exchange Mailbox Database size, its disk space and the whitespace.

The output in table format is sent through email to the recipients mentioned in the script.

Assumptions:
1. A dedicated data drive is used for each database .edb file on the mailbox server.
2. Script will provide the database size and disk space for active node only. Script can be modified to get the details of passive node as well.
3. The drive is hosted on LUN. You can use alternate command/class to get the details in case the database drives is a logical disk.

Why do you need this report:
1. To monitor the disk space you may use any monitoring tools but this script only reports the free disk space of the database drive as against the actual EDB file size.
2. You may not be worried about the whitespace during your initial days of Server/Database setup. However, as the users in the database continute to grow its important to have a report on whitespace to check the database growth and the disk space availability.
3. Based on this report, you could possibly monitor the database whitespace growth and decide on the next steps to recover this whitespace to perform Offline defragmentation or other whitespace recovery options.

#######################################################################################################################
#Title - WhiteSpace and Free disk space of Exchange Mailbox Databases script
# This script sends the report in tabular format to the recipients mentioned in $recipients variable
#######################################################################################################################

# Block of code to send emails using the SMTP relay server
$recipients = "user1@adp.com","user2@domain.com"

#Function declaration for sending emails
function SendMail($recipients)
{
  $date = (get-date).getdatetimeformats()
  $Today = $date[1]
  $SmtpClient = new-object system.net.mail.smtpClient 
  $MailMessage = New-Object system.net.mail.mailmessage 
  $SmtpClient.Host = "SMTPHostName"
  $mailmessage.from = "Fromemailaddress"
  foreach ($i in $recipients) {
  $mailmessage.To.add($i)
  }
  $mailmessage.Subject = "Exchange Database Statistics - $Today"
  $mailmessage.IsBodyHTML = $true
  $mailmessage.Body = $body
  $smtpclient.Send($mailmessage)
}

#Get the list of all clustered mailbox servers
$Error = $null
$MBCluster = Get-MailboxServer | where {$_.ClusteredStorageType -eq "NonShared"} | sort -Property name
$WhiteSpaceObject = @()

$BeginDate=[System.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-1))

foreach ($Server in $MBCluster)
{
$ClusRes = Get-WmiObject -Namespace root\mscluster -Class MSCluster_NodeToActiveGroup -ComputerName $Server | where {$_.PartComponent -like ("*"+$Server+'"')}
#Get the Active node details and declare variables, arrays and hash tables to be used
$MBServer = ($ClusRes.Groupcomponent.split("=")[1]).trim('"')
$WhiteSpaceSummary = @{}
$SGarray = @()
$DBArray = @()
$DBSizeArray = @()
$TotalLUNSizeArray = @()
$TotalLUNFreeArray = @()
$TotalLUNUsedArray = @()
$PercentFreeArray = @()
$WhiteSpaceArray = @()
$SortedWS = @{}
$i=0
$k=0

#Check the event logs on active node for event 1221 - This event ID shows the whitespace of the database
$logs = Get-WmiObject -class win32_ntlogevent -ComputerName $MBServer -Filter "(EventCode=1221) and (LogFile='application') and (TimeGenerated >'$BeginDate')"
foreach ($log in $logs) {
$MBName = $log.message.split("`"")
$WSEndString = $mbname[2].indexof("megabytes ")-6
$WhitespaceMB = $mbname[2].Substring(5,$WSEndString)/1024
$Formatting = "{0:N2}" -f $WhiteSpaceMB
$WhiteSpaceGB = $Formatting
$WhiteSpaceArray += $WhiteSpaceGB
$Sgname = $mbname[1].split("\")
$DBArray += $Sgname[1]
}
$length = $DBArray.length
$WhiteSpaceSummary.Add($DBArray[$i], $WhiteSpaceArray[$i])
for ($i=1; $i -le $length; $i++)
{
for ($j = $i-1; $j -ge 0; $j--)
{
if([string]$DBArray[$j] -ne [string]$DBArray[$i])
{
$WhiteSpaceSummary.Add($DBArray[$j], $WhiteSpaceArray[$j])
}
else
{
$Error = $WhiteSpaceSummary.Add($DBArray[$j], $WhiteSpaceArray[$j])
}
}
}
$SortedWS = [collections.sortedlist]$WhiteSpaceSummary
[array]$SortedDBArray = $SortedWS.Keys
[array]$SortedWSArray = $SortedWS.Values

#Extract the list of Storage Groups within the server. Use this only if you have to report SG names as well in the report
$StorageGroup = Get-StorageGroup -Server $Server | where {$_.Name -notlike "RSG"}
foreach ($SG in $StorageGroup){
$SGarray += $SG.Name
}
[Array]::Sort([array]$SGArray)

#Extract the list of databases, their EDB file location and the size of the EDB File.
$DatabaseList = Get-MailboxDatabase -Server $Server | where {$_.Recovery -notlike "True"} | Sort-Object Name
foreach($DB in $DatabaseList){
$edbfile = $DB.EdbFilePath
$edbfilepath = "`\`\"+$MBServer+"`\"+$edbfile.pathname.Replace(':','$')
$DBSize = Get-ChildItem $edbfilepath
$Size = $DBSize.Length/1024/1024/1024
$DBSize = "{0:N2}" -f $Size
$DBSizeArray += $DBSize
$LUNStartstring = $edbfile.pathname.indexof("\D")+1
$LUN = $edbfile.pathname.substring(0,$LUNStartstring)

#Extract the usage details of the drive where EDB file is present.
#Use this command if your data drives are hosted on LUNs. You can use the class Win32_Logicaldisk instead of Win32_Volume if you have local hard disks.
$LUNDetails = Get-WmiObject -Class Win32_Volume -ComputerName $MBServer | Where-Object {$_.Caption -like $LUN}
$TotalLUNSize = "{0:F2}" -f $($LUnDetails.Capacity / 1GB)
$TotalLUNSizeArray += $TotalLUNSize
$TotalLUNFree = "{0:F2}" -f $($LUnDetails.FreeSpace / 1GB)
$TotalLUNFreeArray += $TotalLUNFree
$TotalLUNUsed = "{0:F2}" -f $(($LUnDetails.Capacity-$LUnDetails.FreeSpace) / 1GB)
$TotalLUNUsedArray += $TotalLUNUsed
$PercentFree = "{0:F0}" -f $(([int64]$LUNDetails.FreeSpace / [int64]$LUNDetails.Capacity)*100)
$PercentFreeArray += $PercentFree
}

for ($k=0; $k -lt 10; $k++)
{
$WhiteSpaceStatus = New-Object System.Object
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $MBServer -Name Server
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $SGArray[$k] -Name StorageGroup
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $SortedDBArray[$k] -Name Database
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $TotalLUNSizeArray[$k] -Name TotalLUNSize
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $TotalLUNUsedArray[$k] -Name TotalLUNUsed
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $TotalLUNFreeArray[$k] -Name TotalLUNFree
[decimal]$EDBFileSize = "{0:N2}" -f $DBSizeArray[$k]
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $EDBFileSize -Name EDBSize
[decimal]$Whitespace = "{0:N2}" -f $SortedWSArray[$k]
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $Whitespace -Name WhiteSpace
[decimal]$ActualDBSize = "{0:F2}" -f $($EDBFileSize - $WhiteSpace)
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $ActualDBSize -Name ActualDBSize
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $PercentFreeArray[$k] -Name PercentFree
$WhiteSpaceObject += $WhiteSpaceStatus
}
}
#This section composes the body of email in HTML Table format.
$h = "<style>"
$h = $h + "BODY{background-color:#FFFFFF;}"
$h = $h + "TABLE{border-width: 1px;border-style: double;border-color: black;border-collapse: collapse;}"
$h = $h + "TH{border-width: 1px;padding: 0px;border-style: double;border-color: black;background-color: #000000}"
$h = $h + "TD{border-width: 1px;padding: 0px;border-style: double;border-color: black;background-color: #000000}"
$h = $h + "</style>"

$a = $WhiteSpaceObject | Sort-Object -Property Database | Foreach-Object -Begin {
$h
"<br><b>Mailbox Database Statistics</b><br>"
"<table>"

"<tr><th>Server Name</th><th>SG Name</th><th>Database</th><th>TotalLUNSize</th><th>TotalLUNUsed</th><th>TotalLUNFree</th><th>EDBSize</th><th>WhiteSpace</th><th>DBSize</th><th>%LUNFree</th></tr>"

$row = 0
} -Process {

$style = "style='background-color: #CCCCCC;text-align: center;'";
if ( $row % 2 -eq 0 )
{
$style = "style='background-color: white;text-align: center;'";
}
[string]$StrLUNSize = $_.TotalLUNSize
$TotalLUNSizeGB = $StrLUNSize +" GB"
[string]$StrLUNUsed = $_.TotalLUNUsed
$TotalLUNUsedGB = $StrLUNUsed +" GB"
[string]$StrLUNFree = $_.TotalLUNFree
$TotalLUNFreeGB = $StrLUNFree +" GB"
[string]$String = $_.WhiteSpace
$WhitespaceGB = $String +" GB"
[string]$StrEDBSize = $_.EDBSize
$EDBSizeGB = $StrEDBSize +" GB"
[string]$StrActualDBSize = $_.ActualDBSize
$ActualDBSizeGB = $StrActualDBSize +" GB"

"<tr><td {0}>{1}</td><td {0}>{2}</td><td {0}>{3}</td><td {0}>{4}</td><td {0}>{5}</td><td {0}>{6}</td><td {0}>{7}</td><td {0}>{8}</td><td {0}>{9}</td><td {0}>{10}</td></tr>" -f $style, $_.Server,$_.StorageGroup,$_.Database,$TotalLUNSizeGB,$TotalLUNUsedGB,$TotalLUNFreeGB,$EDBSizeGB,$WhiteSpaceGB,$ActualDBSizeGB,$_.PercentFree

$row++
} -End {
"</table>"
}

$body = ($a)

sendmail ($recipients)

Open in new window


Additional notes:

 This script can be modified to accomodate all additional details
1. Catalog Folder size
2. Number of mailboxes within the Storage group
3. Sum of size of all mailboxes withing the storage group. This would help you identify the space used for database architecture within the EDB file.
WhiteSpaceInfo.txt
3
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free