Exchange Server Share

July 27, 2009

Exchange 2007: Database Statistics in Powershell

Many times we need to find number of mailboxes in each database and the size of the database (EDB file) of all servers in an Exchange 2007 environment. With PowerShell, it is fairly simple to grab in a single line.

1.  How do we find number of mailboxes in each database?

On Screen: Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Number Of Mailboxes";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Format-Table -AutoSize

Export to CSV: Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Number Of Mailboxes";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Export-Csv C:\ServerStat-1.csv

2.  How do we find number of mailboxes and size of EDB file for each Database?

On Screen: Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1024KB; [math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Format-table -AutoSize

Export to CSV: Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1024KB; [math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Export-CSV C:\ServerStat-2.csv

About these ads

18 Comments

  1. [...] по поддержке почтовой организации. Наткнулся в интернете на пару отличных скриптов для проведения этой [...]

    Pingback by Exchange 2007: статистика почтовых баз | Булдаков.ru | Блог этот исключительно о моей жизни и моей работе — July 28, 2009 @ 2:02 pm

  2. Hi Amit,

    I try to find number of mailboxes and size of EDB file for each Database but no db size, it shows only zeros.

    The number of mailboxes will show just fine.

    I am running ex07 sp1 RU8 CCR.

    Thx,
    Julian

    Comment by Julian — July 30, 2009 @ 12:23 pm

  3. Yep – I see the same thing using the code from example #2. It displays the correct info for a typical server (CAS/HT/MBX), but only the # of mailboxes is correct for the CCR cluster running SP2. The size (GB) and Size (MB) are both zeros, and the database is about 3.5GB in size. It doesn’t seem to matter which server I run the script on.

    Comment by Pat Richard, MVP — August 1, 2009 @ 9:25 am

  4. When I executed the 2nd script, it does show only 0′s in Size in GB and Size in MB col. but No. of mailboxes is just empty. Any idea if I am doing something wrong.

    Comment by Imran — August 2, 2009 @ 1:12 pm

  5. Hi Amit

    The Value $path shows as {\\CMS\c$\DB2\db2.edb, 0}. (CMS =Clustered Mailbox Server)
    This Path dont exist on a CCR Cluster. It should be \\Node1\c$\DB2\db2.edb, or \\Node2\c$\DB2\db2.edb in a CCR evironement.

    Any idea how to change the Script only for databases hostet on a CCR Cluster?

    Thx Georg

    Comment by Georg Fluehman — August 4, 2009 @ 6:39 pm

  6. Any update on CCR based databases? Thanks.

    Comment by Imran — August 5, 2009 @ 12:20 pm

  7. Hi all
    As I needed to get the CCR db sizes I have amended Amits script – hope you dont mind.
    To get around the cluster name issue all you have to do is import a csv with the database identity path and live node as being CCR they should be identical in size. If like myself having 42 seperate databases at little work upfront to get the names and sorted “Get-mailboxdatabase | fl Identity.
    Below is a copy of the script.
    ############################# madferret######
    MBXClusterA\MBXClusterA-SG1\MBXClusterA-DB1,BXClusterA\MBXClusterA-SG1\MBXClusterA-DB2 etc###########
    ########### Node name is either of the CCR cluster nodes###############
    ########### Headers of CSV identity,Nodename##############

    $identity = $Db.identity
    $Nodename = $Db.Nodename
    $colitems = Get-MailboxDatabase $identity | Select Identity, @{Name=”Size”;Expression={$objitem = (Get-MailboxDatabase $Identity); $path = “`\`\” + $Nodename + “`\” + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + “$”+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name=”Sizeb”;Expression={$objitem = (Get-MailboxDatabase $Identity); $path = “`\`\” + “$Nodename” + “`\” + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + “$”+ $objItem.EdbFilePath.PathName.Remove(0,2); $sizeb = ((Get-ChildItem $path).length)/1024KB; [math]::round($sizeb, 2)}}, @{Name=”Mailbox”;expression={(Get-Mailbox -Database $Identity | Measure-Object).Count}}

    if($error.count -gt 0){
    Write-host “Database not present”

    $error.clear()
    }
    else
    {

    foreach ($obj in $colitems)
    {
    Write-Host $obj.Identity, $obj.Size Size “GB” ,$obj.Sizeb Size “MB” ,No of Mailboxes $obj.Mailbox
    } }}

    Comment by madferret — August 5, 2009 @ 6:25 pm

    • Of course not madfereet :)

      Thanks for this, I didn’t get chance to look ahead for CCR…

      Comment by Amit Tank — August 5, 2009 @ 7:09 pm

  8. UPDATED TO WRITE TO EXCEL
    $SourceFile = “c:\CCRDatabases.csv”
    $DBlist = Import-Csv $SourceFile

    $Excel = New-Object -Com Excel.Application
    $Excel.DisplayAlerts = $False
    $Excel.visible = $True
    $Excel = $Excel.Workbooks.Add(1)
    $Sheet = $Excel.WorkSheets.Item(1)
    $Sheet.Cells.Item(1,1) = “Database”
    $Sheet.Cells.Item(1,2) = “Size GB”
    $Sheet.Cells.Item(1,3) = “Size MB”
    $Sheet.Cells.Item(1,4) = “Number of Mailboxes”

    $WorkBook = $Sheet.UsedRange

    $intRow = 2
    foreach ($db in $DBlist)
    {
    ########### Identity is the full exchange path to the database ie MBXClusterA\MBXClusterA-SG1\MBXClusterA-DB1,BXClusterA\MBXClusterA-SG1\MBXClusterA-DB2 etc###########
    ########### Node name is either of the CCR cluster nodes###############
    ########### Headers of CSV identity,Nodename##############

    $identity = $Db.identity
    $Nodename = $Db.Nodename
    $colitems = Get-MailboxDatabase $identity | Select-Object server,storagegroup,Identity, @{Name=”Size”;Expression={$objitem = (Get-MailboxDatabase $Identity); $path = “`\`\” + $Nodename + “`\” + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + “$”+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name=”Sizeb”;Expression={$objitem = (Get-MailboxDatabase $Identity); $path = “`\`\” + “$Nodename” + “`\” + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + “$”+ $objItem.EdbFilePath.PathName.Remove(0,2); $sizeb = ((Get-ChildItem $path).length)/1024KB; [math]::round($sizeb, 2)}}, @{Name=”Mailbox”;expression={(Get-Mailbox -Database $Identity | Measure-Object).Count}}

    if($error.count -gt 0){
    Write-host “Database not present”

    $Sheet.Cells.Item($intRow,2) = Write-Output “Database not present”
    $error.clear()
    }
    else
    {

    foreach ($obj in $colitems)
    {
    Write-Host $obj.Identity, $obj.Size Size “GB” ,$obj.Sizeb Size “MB” ,No of Mailboxes $obj.Mailbox

    $Sheet.Cells.Item($intRow,1) = $identity
    $Sheet.Cells.Item($intRow,2) = $obj.Size
    $Sheet.Cells.Item($intRow,3) = $obj.Sizeb
    $Sheet.Cells.Item($intRow,4) = $obj.Mailbox

    $intRow = $intRow + 1
    $WorkBook.EntireColumn.AutoFit()

    } }}

    Comment by madferret — August 5, 2009 @ 7:08 pm

  9. This time will all the code…… sorry

    ############################# madferret######
    $SourceFile = “c:\CCRDatabases.csv”
    $DBlist = Import-Csv $SourceFile
    foreach ($db in $DBlist)
    {

    ##MBXClusterA\MBXClusterA-SG1\MBXClusterA-DB1,BXClusterA\MBXClusterA-SG1\MBXClusterA-DB2 ##etc###########
    ########### Node name is either of the CCR cluster nodes###############
    ########### Headers of CSV identity,Nodename##############

    $identity = $Db.identity
    $Nodename = $Db.Nodename
    $colitems = Get-MailboxDatabase $identity | Select Identity, @{Name=”Size”;Expression={$objitem = (Get-MailboxDatabase $Identity); $path = “`\`\” + $Nodename + “`\” + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + “$”+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name=”Sizeb”;Expression={$objitem = (Get-MailboxDatabase $Identity); $path = “`\`\” + “$Nodename” + “`\” + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + “$”+ $objItem.EdbFilePath.PathName.Remove(0,2); $sizeb = ((Get-ChildItem $path).length)/1024KB; [math]::round($sizeb, 2)}}, @{Name=”Mailbox”;expression={(Get-Mailbox -Database $Identity | Measure-Object).Count}}

    if($error.count -gt 0){
    Write-host “Database not present”

    $error.clear()
    }
    else
    {

    foreach ($obj in $colitems)
    {
    Write-Host $obj.Identity, $obj.Size Size “GB” ,$obj.Sizeb Size “MB” ,No of Mailboxes $obj.Mailbox
    } }}

    Comment by madferret — August 6, 2009 @ 12:32 am

    • Hey MF. What is the format for the csv file that your script references?

      Comment by Bill — November 2, 2009 @ 7:15 pm

      • How make the csv file, please ?

        Comment by webr — January 15, 2010 @ 6:48 pm

    • This works great, but having trouble trying to get it to write to a txt or csv file.. suggestions..

      Thanks

      Comment by Mike D — December 16, 2009 @ 6:33 pm

  10. I love this script and the output but I need to create a separate txt file for each database or develop a single line script that will provide this output but for a single database.

    Comment by Kelley Underwood — October 17, 2009 @ 9:41 am

  11. Just curious. is there a way to list whitespace?

    Comment by Bill — November 4, 2009 @ 2:26 am

  12. This works, but in which universe is it simple? the imposition by M$ to require admins to start thinking like developers is rapidly driving me to a career change. :(

    Comment by AG — December 2, 2009 @ 5:06 am

  13. [...] Вот пример как посмотреть размер базы из PowerShell на Exchange Server 2007 (взято у Амит-а) [...]

    Pingback by EHLO!, ...about MS Exchange Server in Tbilisi, rep. of Georgia, FYDIBOHF SPDLT ! — March 9, 2010 @ 8:59 pm

  14. [...] Powershell scripts-Determine Store size Exchange 2007: Database Statistics in Powershell Filed under: Exchange,Exchange 2007,Exchange 2010,PowerShell,Script,Tools — Amit Tank @ 11:05 pm [...]

    Pingback by Exchange Powershell scripts-Determine Store size « Swollen Technology — June 19, 2010 @ 7:12 am


RSS feed for comments on this post.

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 32 other followers

%d bloggers like this: