<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Exchange 2007 Mailbox Database size and WhiteSpace using Powershell

Published on
12,641 Points
5,841 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
  • 2
4 Comments

Expert Comment

by:DingoVVarrior
This is a brilliant little script and is exactly what I am looking for.

I need to modify slightly for a non-clustered environment, with many databases on it.

Can you tell me which parts to change, or advise if there is another version for non-clustered servers?

I tried knocking out the cluster part and declaring the $MBServer as a static value but I ended up with an emailed report with no info in it.

Thanks heaps!
0
LVL 16

Author Comment

by:Rajitha Chimmani
Declaring the server name for $MBServer is the correct step provided you are looking for single server. You have to remove the foreach loop in that case. All cluster related commands must be removed and adjusted to single server.

Instead of running the entire script you can try running single commands on your powershell to find out if the desired results are shown.

If you get any output for the below command that means your whitespace details are gathered.

$logs = Get-WmiObject -class win32_ntlogevent -ComputerName $MBServer -Filter "(EventCode=1221) and (LogFile='application') and (TimeGenerated >'$BeginDate')"

So, this way you may have to do for each single component.
0
LVL 16

Author Comment

by:Rajitha Chimmani
I have modified few sections. So, you may try something like the below lines.

#######################################################################################################################
#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 | sort -Property name
$WhiteSpaceObject = @()

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

foreach ($MBServer in $MBCluster)
{
$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 $MBServer | 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 $MBServer | 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

0
LVL 11

Expert Comment

by:Senior IT System Engineer
Hi Rajitha,

Do you have the modified powershell code for Exchange Server 2010 ?
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month