Daily Exchange Mailbox Size Report

There are a plethora of software packages out there to report against your Exchange Infrastructure and many of them are great.  I’ve worked with several in the past.  However, if you want just a simple report for mailbox sizes, and you have access to a SQL Server, you can write your own process.

My script does the following:

1. Check to see if the Exchange Snapin is loaded and loads it if necessary.
2. Build up a connection to the SQL Server.
3. Cycle through each Mailbox Server in your environment.
4. Cycle through each Mailbox Database in your environment for each Server.
5. Cycle through each Mailbox for each Mailbox Database.
6. Execute a single SQL Insert command to add an entry to the table in question.
7. Finally, it closes the SQL Connection and disposes of it.

First, let’s build the SQL Table.  Assuming that you have a database where you have proper credentials, you can just run the following SQL script to create the “MailboxStats” Table.

USE [Scratch]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MailboxStats]
    (
      [Id] [int] IDENTITY(1, 1)
                 NOT NULL ,
      [RunDate] [datetime] NOT NULL ,
      [MailboxGuid] [varchar](36) NOT NULL ,
      [DisplayName] [nvarchar](255) NOT NULL ,
      [ServerName] [nvarchar](255) NOT NULL ,
      [DatabaseName] [nvarchar](255) NOT NULL ,
      [IsArchiveMailbox] [bit] NOT NULL ,
      [DeletedItemCount] [bigint] NOT NULL ,
      [DeletedItemSize] [bigint] NOT NULL ,
      [ItemCount] [bigint] NOT NULL ,
      [MailboxSize] [bigint] NOT NULL ,
      CONSTRAINT [PK_MailboxStats] PRIMARY KEY CLUSTERED ( [Id] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Then all you need to do is run the following script on a scheduled basis. I’ve found that even daily entries do not grow the database to insane sizes too quickly.

########################################################
#
# Daily Mailbox Size Report
#
########################################################

#region Check for Exchange 2010 Snapin
if ( -not ( Get-Command -Name Get-Mailbox -ErrorAction SilentlyContinue ) )
{
    Add-PSSnapin -Name Microsoft.Exchange.Management.PowerShell.E2010 -ErrorAction SilentlyContinue
}
#endregion

#region Script Variables
$RunDate      = Get-Date -Format "MM/dd/yyyy"
$SqlServer    = "SQLSERVERNAME"
$SqlDatabase  = "Scratch"
$SqlTable     = "MailboxStats"
#endregion

$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$( $SqlServer ); Initial Catalog=$( $SqlDatabase ); Integrated Security=SSPI;"
$SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand
$SqlCommand.Connection = $SqlConnection

if ( -not ( $SqlConnection.State -like "Open" ) )
{
    $SqlConnection.Open()
}

#region Cycle Through Servers, then Databases, and finally Mailboxes
$Servers = Get-ExchangeServer | Where-Object { $_.IsE14OrLater -and $_.IsMailboxServer } | Sort-Object -Property Name
$iCount = $Servers.Count; $i = 0
ForEach ( $Server in $Servers )
{
    Write-Progress -Activity "Collecting Server Information" -CurrentOperation "$( $Server.Name )" -Id 1 -PercentComplete ( $i * 100 / $iCount ) -Status "Collecting Databases from Server: $( $Server.Name )"
    $Databases = Get-MailboxDatabaseCopyStatus -Server ( $Server.Name ) | Where-Object { $_.Status -eq "Mounted" } -ErrorAction SilentlyContinue | Sort-Object -Property DatabaseName
    $jCount = $Databases.Count; $j = 0
    ForEach ( $Database in $Databases )
    {
        Write-Progress -Activity "Collecting Database Information" -CurrentOperation "$( $Database.DatabaseName )" -Id 2 -ParentId 1 -PercentComplete ( $j * 100 / $jCount ) -Status "Collecting Mailboxes from Database: $( $Database.DatabaseName )"
        $Mailboxes = Get-Mailbox -ResultSize Unlimited -Database ( $Database.DatabaseName ) -ErrorAction SilentlyContinue | Sort-Object -Property DisplayName
        $kCount = $Mailboxes.Count; $k = 0
        $lCount = $ArchiveMailboxes.Count; $l = 0
        ForEach ( $Mailbox in $Mailboxes )
        {
            Write-Progress -Activity "Collecting Mailbox Information" -CurrentOperation "$( $Mailbox.DisplayName )" -Id 3 -ParentId 2 -PercentComplete ( $k * 100 / $kCount ) -Status "Collecting Statistics from Mailbox: $( $Mailbox.DisplayName )"
            $MailboxInfo = $Mailbox | Get-MailboxStatistics | Select-Object DisplayName, MailboxGuid, ServerName, DatabaseName, IsArchiveMailbox, DeletedItemCount, @{ Name = "DeletedSize"; Expression = { $_.TotalDeletedItemSize.Value.ToBytes() } }, ItemCount, @{ Name = "MailboxSize"; Expression = { $_.TotalItemSize.Value.ToBytes() } }
            if ( $MailboxInfo )
            {
                $DisplayName = $MailboxInfo.DisplayName.Replace("'", "''")
                $ArchiveBit = 0
                $SqlInsert = "INSERT INTO [dbo].[MailboxStats] ([RunDate], [DisplayName], [MailboxGuid], [ServerName], [DatabaseName], [IsArchiveMailbox], [DeletedItemCount], [DeletedItemSize], [ItemCount], [MailboxSize]) VALUES ('$( $RunDate )', '$( $DisplayName )', '$( $MailboxInfo.MailboxGuid.guid )', '$( $MailboxInfo.ServerName )', '$( $MailboxInfo.DatabaseName )', $( $ArchiveBit ), $( $MailboxInfo.DeletedItemCount ), $( $MailboxInfo.DeletedSize), $( $MailboxInfo.ItemCount ), $( $MailboxInfo.MailboxSize))"
                $SqlCommand.CommandText = $SqlInsert
                $SqlCommand.ExecuteNonQuery() | Out-Null
                $MailboxInfo = $Mailbox | Get-MailboxStatistics -Archive -ErrorAction SilentlyContinue | Select-Object DisplayName, MailboxGuid, ServerName, DatabaseName, IsArchiveMailbox, DeletedItemCount, @{ Name = "DeletedSize"; Expression = { $_.TotalDeletedItemSize.Value.ToBytes() } }, ItemCount, @{ Name = "MailboxSize"; Expression = { $_.TotalItemSize.Value.ToBytes() } }
                if ( $MailboxInfo )
                {
                    $ArchiveBit = 1
                    $SqlInsert = "INSERT INTO [dbo].[MailboxStats] ([RunDate], [DisplayName], [MailboxGuid], [ServerName], [DatabaseName], [IsArchiveMailbox], [DeletedItemCount], [DeletedItemSize], [ItemCount], [MailboxSize]) VALUES ('$( $RunDate )', '$( $DisplayName )', '$( $MailboxInfo.MailboxGuid.guid )', '$( $MailboxInfo.ServerName )', '$( $MailboxInfo.DatabaseName )', $( $ArchiveBit ), $( $MailboxInfo.DeletedItemCount ), $( $MailboxInfo.DeletedSize), $( $MailboxInfo.ItemCount ), $( $MailboxInfo.MailboxSize))"
                    $SqlCommand.CommandText = $SqlInsert
                    $SqlCommand.ExecuteNonQuery() | Out-Null
                }
            }
            $k++
        }
        Write-Progress -Activity "Collecting Mailbox Information" -CurrentOperation "$( $Mailbox.DisplayName )" -Id 3 -ParentId 2 -Completed -Status "Completed"
        $j++
    }
    $i++
    Write-Progress -Activity "Collecting Database Information" -CurrentOperation "$( $Database.Name )" -Id 2 -ParentId 1 -Completed -Status "Completed"
}
Write-Progress -Activity "Collecting Server Information" -CurrentOperation "CurrentOperation" -Id 1 -Completed -Status "Completed"
#endregion

#region Close SQL Connection
$SqlConnection.Close()
$SqlConnection.Dispose()
#endregion

Once the script is run, each record will look something like this:

You can see that there are separate records for the Primary and Archive Mailbox. This script should run in either Exchange 2010 or Exchange 2013, though I’ve not had opportunity to check on the latter as yet.

The script is pretty straight forward, but if you have any questions, all you need to do is ask.

Until next time, Ramblers.