Exchange 2010 and 2013 Database Growth Reporting Script


Introduction

Often times in Exchange Support we get cases reporting that the size of one or more Exchange databases is growing abnormally. The questions or comments that we get will range from “The database is growing in size but we aren’t reclaiming white space” to “All of the databases on this one server are rapidly growing in size but the transaction log creation rate is “normal”. This script is aimed at helping collect the data necessary in determining what exactly is happening. For log growth issues, you should also reference Kevin Carker’s blog post here.

Please note when working with Microsoft Support, there may still be additional data that needs to be captured. For instance, the script does not capture things like mailbox database performance monitor logging. Depending on the feedback we get, we can always look at building in additional functionality in the future. Test it, use it, but please understand it is NOT officially supported by Microsoft. Most of the script doesn’t modify anything in Exchange, it just extracts and compares data.

Note: The space dump function will stop (and then restart) the Microsoft Exchange Replication service on the target node and replay transactions logs into a passive copy of the selected database, so use this with caution. We put this function in place because the only way to get the true white space of a database is with a space dump. People often think that the AvailableNewMailboxSpace is the equivalent of whitespace, but as Ross Smith IV notes in his 2010 Database Maintenance blog “Note that there is a status property available on databases within Exchange 2010, but it should not be used to determine the amount of total whitespace available within the database. AvailableNewMailboxSpace tells you how much space is available in the root tree of the database. It does not factor in the free pages within mailbox tables, index tables, etc. It is not representative of the white space within the database.” So again, use caution when executing that function of script as you probably don’t want to bring a lagged database copy into a clean shutdown state, etc.

Before we get into an example of the script, I wanted to point out something you should always check when you are troubleshooting database growth cases – what is the total deleted item size in the database and are any users on Litigation hold.

The following set of commands will export the mailbox statistics for any user that is on Litigation Hold for a specific database and furthermore will give you the sum of items in the recoverable items folder for those users (remember we use the subfolders Versions and Purges when Lit Hold is enabled).

1. Export the users mailbox statistics per database that have litigation hold enabled

get-mailbox -database <database name> -Filter {LitigationHoldEnabled -eq $true} | get-mailboxstatistics | Export-CSV LitHoldUsers.csv

2. Import the new CSV as a variable:

$stats = Import-Csv .\LitHoldUsers.csv

3. Get the sum of Total Deleted Item Size for the Lit Hold users in the spreadsheet

$stats | foreach { $bytesStart = $_.TotalDeletedItemSize.IndexOf("(") ; $bytes = $_.TotalDeletedItemSize.Substring($bytesStart + 1) ; $bytesEnd = $bytes.IndexOf(" ") ; $bytes = $bytes.Substring(0, $bytesEnd) ; $bytes } | Measure-Object –Sum

This will give you the sum for the specific database of recoverable items for users on litigation hold. I’ve seen cases where this amount represented more than 75% of the total database size. You also want to confirm what version of Exchange you are on. There was a known store leak fix that was ported to Exchange 2010 SP3 RU1. I don’t believe the KB is updated with the fix information, but the fix was put in place, so before you start digging in too deep with the script, make sure to install SP3 RU1 and see if the issue continues.

Ok moving onto the script. What can the script do you ask? The script can do the following:

  • Collects mailbox statistics across the specified database, adding mutable note properties for future use in differencing
  • Collects database statistics for the specified database, adding mutable note properties for later differencing.
  • Collects mailbox folder statistics for all mailboxes on the specified database, adding mutable properties for later differencing
  • Compares size and item count attributes of the input database from the differencing database, returning a database type object with the modified attributes
  • Compares size and item count attributes of the input mailbox from the differencing mailbox, returning a mailbox type object with the modified attributes
  • Compares size and item count attributes of the input folder from the difference folder, returning a folder type object with the modified attributes.
  • Compares size and item count attributes of the input report from the difference report, returning a report type object with the modified attributes.
  • Exports a copy of a report (database, mailbox, and folder statistics) to the specified path or current directory in *.XML format
  • Imports an *.XML report and exports it to *.CSV format.
  • Imports the report details from the specified file path (database, mailbox, and folder statistics)
  • Outputs database details and top 25 mailboxes by size and top 25 folders by size
  • Collects a space dump, ESEUTIL /MS, from a passive copy of the specified database and writes to *.TXT
  • Searches for events concerning Online Maintenance Overlap and Possible Corruption, outputting them to the screen
  • Collects and exports current Store Usage Statistics to *.CSV

You can download the script from here.

Sample script run

Issue reported: “Mailbox Database 0102658021” is rapidly growing in size.

  • List options to use with -mode switch:

image

  • Choose Collect and Export the data for the database that is growing in size (enter mode 1)
  • Specify a path or use the current working directory. Quotes around the path is optional, but the path must already exist.
  • Specify the database name. It will run against the active copy. Quotes are optional.

image

  • Depending on the size of the database, folder counts, etc. this could take some time to run from here. Once the report is generated you will be prompted to select the top # of items to display from each report. 25 is the default if you just press enter.

image

  • The onscreen reports will now generate. Note DB size on disk here is 1.38GB.

clip_image002

The onscreen reports that you can scroll through include the Database size details and individual reports for the Top 25 of the following: mailboxes by item size, mailboxes by DeletedItemSize, mailboxes by item count, mailboxes by deleted item count, mailboxes by associated items, Folders by size, Folders by item count, and Folders by deleted item count.

The Full XML report will be stored in the location you specified.

image

If you close out of the PowerShell window and wish to review the reports again, just run the script in mode 2 (quotes are optional).

image

Now we have a valid report at a single point in time of what's going on in the database. Since we are troubleshooting a “Database Growth” issue, we will need to wait some time for the database to grow. If you have ample space on the database drive, then I would run the report every 24 hours.

Once you ready, compile a second report of the database (same way you did the first above)

Press enter for top 25 items and the onscreen report will start scrolling through. As you can see below our database size increased on disk from 1.38 GB to 1.63 GB.

image

So what grew? Well now we will use Mode 3 of the script to compare the 2 XML reports. Note the second XML report in the directory:

image

Run the script with –mode 3. You will be prompted to enter the full file path for the original report and then the second report after the DB growth was recognized.

image

Once the differential is completed you will see a report that is similar to the first two reports. Keep in mind this is a DIFFERENTIAL Report, so it is reporting on how many items in a particular folder grew or how much the DB grew, etc.

image

As you can see above the size on disk shows 256mb. This is actually how much the database grew as we know that it went from 1.38gb to 1.63gb. If I scroll through the reports, I can see that the Administrator mailbox is where most of the growth took place (which is where I added the content).

image

This data can be used to tell what user(s) might be causing the additional growth. As noted earlier, we have had some “phantom” growth cases as well where we had known store leaks which is why it is imperative to make sure you have installed Exchange 2010 SP3 RU1. Its possible that you could run into that type of scenario here, but the data should support that as you would see DB on disk grow but no real growth in the mailboxes at which point you would need to engage Microsoft Support.

A quick note on the Actual Overhead value. This is calculated by taking the physical size of the database and subtracting the AvailableNewMailboxSpace, TotalItemSize and TotalDeletedItemSize. Remember that AvailableNewMailboxSpace is not the true amount of whitespace, so the actual number may be a little higher than what is reported here.

Other script parameters

The remaining modes of the script should be pretty self explanatory.

Mode 4 – Export Store Usage Statistics just uses the built in Get-StoreUsageStatistics function allowing you to run it at a server or database level.

Mode 5 – Will search the application log for events concerning Online Maintenance Overlap and Possible Corruption, outputting them to the screen. We probably didn’t get every event listed here, so we can add events as we see them.

Mode 6 – Will search the server that it is run on for passive copies of databases. It will alert you to any that are configured as lagged copies. If you choose to run this against a passive copy to get the true white space, then it will stop the Microsoft Exchange Replication service, do a soft replay of logs needed to bring the passive copy into a clean shutdown, and then run an ESEUtil /MS against the passive copy. Once completed it will restart the Replication service.

Mode 7 – will just read in one of the XML reports created from Mode 1 and break it out into its individual component reports in CSV format.

Jesse and I decided to build this because we continue to see cases on database growth, so a special thanks to him for running with the idea and compiling the core components of the script. We both had been running our own versions of this while troubleshooting cases, but alas, his core script was better (I still got to add some of the fun ancillary components). We’d like to thank Bill Long for planting the idea in our heads as he worked so many of these cases from a debugging standpoint as well as David Dockter and Rob Whaley for their technical review.

Hopefully this helps you troubleshoot any database growth issues you run across. We look forward to your comments and are definitely open to suggestions on how we can make this better for you.

Happy Troubleshooting!

Jesse Newgard and Charles Lewis
Sr. Support Escalation Engineers

Comments (37)
  1. Anonymous says:

    Great article for Exchange On-Premises customers :-)

  2. Anonymous says:

    The gist I am getting is that Exchange onsite is just getting less and less love. Porting PowerShell commands to a G-U-I seems like a no-brainer, especially when combined with the required G-U-I Windows OS. Making this script compatible with the EAC and providing a good HTML reporting tool seems like the way we should be going. Is it really that hard to integrate these reports into the EAC? Is the Exchange 2013 that limited or is it just that Office 365 is the only things that matters anymore? I just can’t believe we are in the year 2014 and we have to manually dump XML reports and figure out our own convert to HTML methods. Simply put, it sounds like lazy programmers and/or resource constraints with the Exchange project group. Perhaps they are just too management heavy now to make products that are not half-assed anymore with half-baked methodologies that require us all to go backwards into the 1980’s.

  3. Anonymous says:

    Please put tools such as this along with all of the previous Exchange 2010 toolbox tools back into the EAC. Regardless of how you feel about gui or command-line based administration, it’s ridiculous we have less visual functionality in Exchange 2013 than we did in Exchange 2010 on a Windows OS that is gui-based and has been for 20+ years now. If you want Exchange to be command line PS-based only, then please port it to Linux and let’s ditch Windows altogether.

  4. Anonymous says:

    until we can install exchange on a core mode windows, i think everyone should just shut the hell up about forcing powershell on us. makes you sound retarded.

  5. Anonymous says:

    “one day we’ll get rid of that memory hungry GUI once and for all.”

    Isn’t the GUI the only reason why anyone actually uses Windows? If there’s no GUI…maybe I should think about using Linux. Oh wait!…they are working on enhancing the GUI there.

  6. Anonymous says:

    ” All Microsoft products should adopt powershell interfaces.”

    I agree. Let’s start with the flagship Office 365 and remove the EAC from there first.

  7. Anonymous says:

    I always love to see how many “soon to be obsolete” it people *** and whine about GUI versus powershell. If you are so dependent on the GUI to administer exchange, you should start now retraining for a new career. All Microsoft products should adopt powershell interfaces. Being able to automate menial tasks and control access to commandets via roles and rbac is the future of these products. Why should I manage five GUIs when one powershell interface lets manage active directory lync and exchange and script against each other object integrations. You are seriously dating yourself by refusing to learn new and useful technologies

  8. Anonymous says:

    And the reason this process isn’t GUI-based with nice wizards and a help menu system is because…???

    Is this Windows or Linux?

  9. Anonymous says:

    Hey guys…good work writing this script. I’d say the Exchange PG should be ashamed this is not in the EAC/EMC.

    Question – I ran this in my environment because I have precisely such an issue ongoing in my environment right now. We have about 550GB of mailbox+deleted items+whitespace but the EDB file is 1.1TB. We have checked and ruled out the usual suspects. The overhead from your script shows almost 58% compared to the recommended 20%. Any thoughts?

  10. Anonymous says:

    Thank you for the troubleshooting script. Unlike those that fail to understand it is an unsupported (as clearly stated) tool to be used for a specific purpose, I will happily (and easily) use it via Powershell. Oh the horror that this unsupported, specific tool is not built into the GUI that isn’t as flexible as PS. Whah! Whah!

  11. Anonymous says:

    Thanks for the script. Question One. Is there a method for performing this for hundreds of databases across multiple Exchange servers at the same time? Running this script against a single database is great, but I have over 300 databases. Question Two. Will this script work on Exchange 2010 databases as well?

  12. Anonymous says:

    "it is an unsupported (as clearly stated) tool " Yes, I can clearly understand why wanting to know which users are consuming the most space would be only available via an unsupported method. That makes perfect sense. Why would ANYONE want that officially
    supported feature in the shipping product that is on it’s 20th year of release?

  13. Anonymous says:

    This is good for Exchange Server 2010/2013 Admins.
    Thanks alot :-)

  14. Anonymous says:

    “one day we’ll get rid of that memory hungry GUI once and for all.”

    So, Windows Server is planned to be discontinued then? Why don’t we go back to punchcards while we are at it too. There is a reason we run Windows server and that reason is for the GUI. Take away the GUI and you don’t have Windows anymore, you have something akin to NetWare 3.x which isn’t doing so well these days.

  15. Anonymous says:

    “Yes, I can clearly understand why wanting to know which users are consuming the most space would be only available via an unsupported method. That makes perfect sense. Why would ANYONE want that officially supported feature in the shipping product that is on it’s 20th year of release?”

    Five seconds of typing via PS is an unsupported method to get mailbox sizes?

  16. Anonymous says:

    Hi what are ‘AssociatedItems’ ?
    And how to show them?

    Some users have several thousand items of this object.
    It is possible or recommended to clear this configuration settings?

  17. Anonymous says:

    Oh and yes, it will run against 2010.

  18. Anonymous says:

    Regardless if it is 5 seconds or 5 million seconds, the fact that basic information about mailbox sizes and database growth is STILL UNSUPPORTED in an Enterprise class e-mail product as a standard basic feature is just nuts.

    I find the entire Exchange 2013 product group to be severely subpar to what they have delivered in the past. Exchange 2003 was good. Exchange 2007 was even better, but with complicated clustering. Exchange 2010 was just about perfect. Exchange 2013 is like we’re stepping backwards and the EAC is clearly inferior to the EMC/EMS we had in the past. No MAPI. No POP. The web experience for the clients is subpar as well with the new “Metro” look even if it does work on more devices. Profiles now show some random hexadecimal ID number rather which confused people and makes troubleshooting even more difficult.

    It’s simply an inferior product to what we had in the past all around. It appears that it’s only purpose is to push people to the cloud and Office 365 and they have crippled their core product intentionally to accomplish this goal.

  19. Anonymous says:

    Thanks for the script! This will help tremendously.

  20. Anonymous says:

    thanks for the hard work

  21. Anonymous says:

    Not to add to some of the other criticisms which seem to have nothing to do with this specific post, but I am a bit confused as to why a nice guy reporting toolset that has all of these power shell capabilities built into it has not been introduced for Exchange 2013. We’re forced to find these scripts on blogs such as this and then we have to hack them together to make a solution. I’m sure anyone competent on the Exchange product group could easily port this into a standard EAC toolset with very minimal effort. I also need to point out to the Exchange team that we’ve been begging for this kind of functionality ever since Exchange 2013 came out and while it’s great to see this toolset finally available, it’s not full baked yet and it should have been placed into the Exchange 2013 RTM bits from the get-go.

  22. Anonymous says:

    The script will in fact do more than 25 in the reporting. 25 is just the default which is why we give you the option of specifying your own number. The script was designed to help troubleshoot database growth issues and can be used in a large environment.

  23. Anonymous says:

    Once you surpass 30% for overhead, you are likely experiencing a bloat scenario. This is growth outside of content and is not recoverable by normal Store Maintenance. If overhead doesn’t continue to grow, the space can be recovered most easily by standing up a new database and moving mailboxes. If growth does continue, recovering the space works the same way, however, if after setting up a new database you see the same overhead growth follow, it may be time to work with support.

  24. Anonymous says:

    The thought process behind our initial design was to use this to troubleshoot an issue for a specific database having an “unknown” growth issue. We weren’t thinking about scaling against a large number of databases at the same time. That is a great suggestion that we need to look into. We’ve also thought about building a comparison function to compare store usage statistics against the existing differential reporting.

  25. Anonymous says:

    This would all be so much easier with a simple, intuitive Windows-based admin console.

    For example, I can run the script in mode 1 to export into an XML, but if I want to run mode 2 I have to type in the whole wonky path with spaces and everything to find the XML file it just saved? Then to run mode 3 we also have to type in the entire damn path all over again.

    Can someone, ANYONE on the Exchange team please explain to me why this has not been integrated into the EAC yet as a standard gui tool? Why can we not just simply browse to the file or have that file cached?

    It seems like everything in Exchange 2010 was perfect and now everything that was an automated process has been stripped out of Exchange 2013 and we now are forced to use command line manual methods to perform tasks? This script only captures the top 25 mailboxes making it COMPLETELY USELESS IN LARGE ORGANIZATIONS! It doesn’t even offer an HTML view. We have to run yet ANOTHER command to convert to CSV? Who thought of this garbage?

    It’s all command line junk that has been very poorly thought out. I am so disappointed in Exchange 2013 even after update 4 that I could scream. EAC and OWA both look terrible compared to Exchange 2010 and now even the tools require multiple manual steps with complex typing involved and are beyond limiting in scope. It wasn’t enough that you removed most of the toolbox, you had to make the new methods suck too.

  26. Anonymous says:

    ” There was a known store leak fix that was ported to Exchange 2010 SP3 RU1 ”

    This is ridiculous why such information is not publicly available. I have a Exchange 2010 SP2 RU7 setup experiencing this issue. However in a tightly controlled environment like ours…I cannot justify going to the next service pack/rollup unless such issues are publicly documented.

    Do you think a statement like “don’t believe the KB is updated with the fix information” is going to fly? I wish Microsoft would stop acting like amateurs.

  27. Anonymous says:

    Thanks, nice script, keep up the great powershell work, one day we’ll get rid of that memory hungry GUI once and for all.

    Question about the “database leak” which isn’t openly published in the KB – it concerns me that there are fixes being released that we’re not being made aware that we’re installing. How often is this happening?

  28. Anonymous says:

    Associated Items are messages in the Associated Contents Table of the folder. This table typically contains items used to store configuration data for both Outlook and 3rd Party software. Normally, these are hidden but you can use a tool like MFCMAPI to login to the output profile and take a look. Without knowing what the messages are for, we can’t say if the number is abnormal or not. High item counts can always affect performance.

    Folder-Associated Information Tables
    http://msdn.microsoft.com/en-us/library/office/cc842325.aspx

    MFCMAPI
    http://mfcmapi.codeplex.com/

    Note: Updates to the script are currently being planned.

  29. Anonymous says:

    There are shortcuts that make working with the paths in question much quicker.
    1. Shift + Right-Click > Copy As Path allows you to grab the full path of a file or folder
    2. Enable Quick Edit Mode in Exchange Management Shell: Righ-Click the EMS Window Icon and go to Properties; Check Quick Edit Mode
    2a. This allows you to Mark, Copy, and Paste without using the context menu
    2b. Left-Click and Drag to Mark, Right-Click with a selection Marked to Copy, Right-Click with no selection to Paste

    Get the Full Path of a Folder or File in Windows 7 [Works in other versions]
    http://technet.microsoft.com/en-us/magazine/ff678296.aspx

    HOW TO: Enable and Disable QuickEdit Mode for MS-DOS Programs in Windows 2000 [This functionality is the same for both Command Prompt and PowerShell]
    http://support.microsoft.com/kb/317591/en-us

  30. Anonymous says:

    Our NetWare 6.5 SP8a servers are more stable than our Windows 2008 R2 servers. And our Novell SLES 11 Linux servers blow away our Windows 2012 servers.

  31. Johnny Left says:

    Great Article and script

  32. Ziv Rivkis says:

    First of all, thank you for all the hard work that you’ve put into this product. A couple of days after you published this article I had to use it to troubleshoot a DB bloat issue.
    Couple of comments:
    1. When the script is ran in Mode 3 then the “removals” (changes with a “-” sign) are not converted to MB/GB and remain as B. Not a big deal but something to look into.
    2. When running the script in Mode 1 the following error is received: Method invocation failed because [System.String] doesn’t contain a method named ‘ToBytes’.
    At U:ScriptsExchangeDatabaseGrowthReporting.PS1:145 char:112
    + $mbxFolderStat | %{Add-Member -InputObject $_ -MemberType NoteProperty -Name Size -Value $_.FolderSize.ToBytes <<< e $_.ItemsInFolder; Add-Member -InputObject $_ -MemberType NoteProperty -Name DeletedItemCount -Value $_.DeletedItemsInF + CategoryInfo : InvalidOperation: (ToBytes:String) [], RuntimeException + FullyQualifiedErrorId : MethodNotFound

  33. AnonymousProfessional says:

    “until we can install exchange on a core mode windows, i think everyone should just shut the hell up about forcing powershell on us. makes you sound retarded”

    You sound like quite the professional. When you need to do more than one off changes in your system, do you sit at the GUI for hours/days to finish repetitive tasks too? You seem to think PS is a bad thing, when it lets you get real work done. Pointing and clicking for endless hours isn’t what a professional Exchange admin does. Learn the most efficient process to do the job and stop wasting your employer’s time and money.

  34. AnotherAnonymousAdmin says:

    AnonymousProfessional,

    I agree with you. Office 365 should be converted IMMEDIATELY to PowerShell only as an example of how the “future” state of things should be. Once everyone sees how convenient scripts are that will surely sell even MORE licenses. Comeon, “man-up” and let’s get this show on the road! PowerShell all the way baby and Windows be damned.

  35. Anonymous says:

    I agree too. Both our admins and end users are clamoring for a PS-based scripting environment only. Please have Windows 9 be GUI-less and make the next version of Office so we have to script Word. We are so damn tired of having Windows. It will be so refreshing when we all update to the future PS-based model. We are planning on saving a fortune on monitors because we won’t even need color anymore. Definitely rip out all the EAC and other GUI elements of Exchange as fast as possible so we can all be more productive and not have to waste our time with any GUI methodologies. Smart move.

  36. PSS says:

    Dear MS and Exchange Team, listen to your customers, and experts like Tony Redmond, and provide out of the box reporting capacities that show this pretty basic tasks in the admin space, MS you already know that need from your own PSS engineers and Engineering
    teams that manage your own Office 365 infrastructure.

  37. JN013 says:

    Ziv Rivkis, Thanks so much for your feedback. We’re working on updates and will be sure to look into what you’ve provided and will work it into the upcoming version.

Comments are closed.