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:

Id RunDate MailboxGuid DisplayName ServerName DatabaseName IsArchiveMailbox DeletedItemCount DeletedItemSize ItemCount MailboxSize
20501 2014-02-25 00:00:00.000 69633637-b359-4654-8dad-eee2dab98502 Sparenberg, Kevin M. MBXSERVER02v DB02 0 10069 40603360 63191 1249148371
20502 2014-02-25 00:00:00.000 38c4add6-4dad-486a-afae-0770136fafd3 Sparenberg, Kevin M. MBXSERVER02v DB05 1 652 1879831 11985 264359378

 

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.

2 thoughts on “Daily Exchange Mailbox Size Report”

  1. Helpful article, thanks for the wonderful information, that provides the script related to exchange mailbox size report, I tested this tool from http://www.lepide.com/exchange-reporter/ that allows to generate of exchange mailbox reports as per requirement and view status of scheduled reports (success or failure). This tool helps to analyze mailboxes, attachment size, mailbox folder, and Outlook Web access (OWA) with the help of the reports generated and filter the reports up to the granular level.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.