SharePoint 2010–Powershell to determine Database Sizes in Farm

Earlier I wrote about how to use Powershell to dump out the Site Collection Storage Usage for a Farm, and this works well for when you want to analyze your farm usage in terms of capacity management and being able to manage your Site Collections, but one of the other key areas of SharePoint operations is to backup those site collections and know that you’ve got enough space for your backups and restores.  In this case you’ll want to become familiar with a couple different PowerShell commandlets, Get-SPContentDatabase (for only content databases) and Get-SPDatabase (for all databases in the farm).

The first step is to grab a database and pipe it through the Get-Method in order to explore what items are available to us:

$db = GetSPContentDatabase WSS_Content

$db | Get-Method

And again we’ll get a nice list of objects in the $db

TypeName: Microsoft.SharePoint.Administration.SPContentDatabase

Name                                MemberType    Definition
—-                                ———-    ———-
Type                                AliasProperty Type = TypeName
AddBackupObjects                    Method        System.Void AddBackupObjects(Microsoft.SharePoint.Administration.B…
AddFailoverServiceInstance          Method        System.Void AddFailoverServiceInstance(string failoverServerInstance)
ChangeDatabaseInstance              Method        System.Void ChangeDatabaseInstance(string databaseServiceInstance)…
Clone                               Method        System.Object Clone()
Delete                              Method        System.Void Delete()

…

DiskSizeRequired                    Property      System.UInt64 DiskSizeRequired {get;}

…

Username                            Property      System.String Username {get;set;}
Version                             Property      System.Int64 Version {get;}
WarningSiteCount                    Property      System.Int32 WarningSiteCount {get;set;}
WebApplication                      Property      Microsoft.SharePoint.Administration.SPWebApplication WebApplicatio…

Now, from here we know we’re going to use the Get-SPDatabase in combination with the DiskSizeRequired property to list out all the database in the farm and what their sizes are in MB:

Get-SPDatabase | select name, @{label = "Size";Ex = {$_.disksizerequired/1MB}}

And that will give us a nice listing of the databases in the farm and what their sizes are:

StateService_df20cdbfe7924603acb8e40a3f092610                                                                       514
Secure_Store_Service_DB_95a60957097147eb9abcb43933ec7b53                                                            514
WordAutomationServices_d87bdc383c60493bb47261ace4bb55b0                                                             513
SharePoint_SocialDB                                                                                                 516
Bdc_Service_DB_8fb8f804e77e441d86cea9a2e9a2c5e6                                                                     515
WebAnalyticsServiceApplication_StagingDB_8cae3fa4-a0b1-4…                                                         849
SharePoint_Config                                                                                                  2537
PerformancePoint Service Application_da9632c7c3d64ee299f…                                                         513
FAST_Content_SSA_CrawlStoreDB_a5ebd4c681334fc58b910003f8…                                                        4537
Fast_Query_SSA_CrawlStoreDB_863e0102aa56484fb8a3cc5bb98f…                                                         931
FAST_Content_SSA_PropertyStoreDB_5786a7dfbd504b879c2f0cb…                                                         520

…

At this point I could stop and bring up calc to add up the row of columns, but as a developer we’re supposed to be lazy by nature, why do something the computer can do for us?  So how do we take each database object and sum up the DiskSizeRequired property so we can get the total footprint of all databases in the farm?  Turns out it’s pretty easy thanks to the ForEach-Object which allows us to set up a loop to run through all of our databases that we pipe into it:

Get-SPDatabase | ForEach-Object {$db=0} {$db +=$_.disksizerequired; $_.name + " – " + $_.disksizerequired/1MB} {Write-Host "`n`nTotal Size: " ("{0:n0} MB" -f ($db/1MB))}