Exchange Server Share

… Information sharing on Exchange Server …

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

Written by Amit Tank

July 27, 2009 at 11:05 pm

18 Responses

Subscribe to comments with RSS.

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

  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

    Julian

    July 30, 2009 at 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.

    Pat Richard, MVP

    August 1, 2009 at 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.

    Imran

    August 2, 2009 at 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

    Georg Fluehman

    August 4, 2009 at 6:39 pm

  6. Any update on CCR based databases? Thanks.

    Imran

    August 5, 2009 at 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
    } }}

    madferret

    August 5, 2009 at 6:25 pm

    • Of course not madfereet :)

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

      Amit Tank

      August 5, 2009 at 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()

    } }}

    madferret

    August 5, 2009 at 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
    } }}

    madferret

    August 6, 2009 at 12:32 am

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

      Bill

      November 2, 2009 at 7:15 pm

      • How make the csv file, please ?

        webr

        January 15, 2010 at 6:48 pm

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

      Thanks

      Mike D

      December 16, 2009 at 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.

    Kelley Underwood

    October 17, 2009 at 9:41 am

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

    Bill

    November 4, 2009 at 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. :(

    AG

    December 2, 2009 at 5:06 am

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

  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 […]


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 34 other followers

%d bloggers like this: