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))}