Recently on the MSDN SharePoint forums someone was asking about backup size, and how much space a farm backup would take. This got my wheels turning; there must be a way to do this with Windows PowerShell. To the SharePoint Management Console I went, with purpose in my heart.
I've used Get-SPContentDatabase a few times in the past so that's where I started. I piped it through Get-Member and found a disksizerequired property. The lightbulb when off. That property tells us how much space a database is actually using, not the size of the MDF file itself. This is exactly what I needed. Then I realized not only was there a Get-SPContentDatabase there was also a Get-SPDatabase (no content) that covers all the databases, including the big one, the logging database. After some noodling around I came up with this little gem:
Get-SPDatabase | Sort-Object disksizerequired -desc | Format-Table Name, @{Label ="Size in MB"; Expression = {$_.disksizerequired/1024/1024}}
This lists all of your databases, from largest to smallest and their size in MBs. Its output looks like this:
Name Size in MB
---- ----------
WSS_Logging 1657
SharePoint_Config 505
Search_Service_Application_PropertyS... 158
SharePoint_AdminContent_9cdc3220-ff2... 99
Search_Service_Application_DB_79408a... 99
WSS_Content 74
WSS_Content_OOTB_upgrade 46
WSS_Content_ac5e33ae4e354dd493b6da17... 26
wss_content_upgrade 26
WSS_Content_portal 24
Search_Service_Application_CrawlStor... 17
User Profile Service Application_Pro... 7
Bdc_Service_DB_a95c44b879ef48f8a5307... 6
WebAnalyticsServiceApplication_Repor... 6
WebAnalyticsServiceApplication_Stagi... 5
Application_Registry_Service_DB_2f70... 4
Managed Metadata Service_c23b61fed61... 3
managed_metadata_the_truth 3
User Profile Service Application_Soc... 3
User Profile Service Application_Syn... 2
StateService_783e168d26fd498b9be3061... 2
Secure_Store_Service_DB_89dc97ed2262... 2
PerformancePoint Service Application... 2
WordAutomationServices_00a4e73d0d734... 2
That's pretty helpful, but it doesn't answer that burning question, how much space is needed. I came up with this to answer that question:
Get-SPDatabase | ForEach-Object {$db=0} {$db +=$_.disksizerequired; $_.name + " - " + $_.disksizerequired/1024/1024} {Write-Host "`nTotal Storage (in MB) =" ("{0:n0}" -f ($db/1024/1024))}
Its output looked this this:
StateService_783e168d26fd498b9be3061299862269 - 2
Secure_Store_Service_DB_89dc97ed22624025ae6e9a69f2684978 - 2
WordAutomationServices_00a4e73d0d7341cdac915e8247da5211 - 2
User Profile Service Application_SocialDB_f2c646049bfd4456b612e454ac1a73fd - 3
Bdc_Service_DB_a95c44b879ef48f8a53070abd98a9d03 - 6
WebAnalyticsServiceApplication_StagingDB_51bf4216-80fa-45d5-b580-b5d419c8e269 - 5
SharePoint_Config - 505
PerformancePoint Service Application_c575f52198844d46a6c2d29c69a6594a - 2
Search_Service_Application_CrawlStoreDB_6c608154693c412bb9fc1e35235e502f - 17
Search_Service_Application_PropertyStoreDB_d7726abcc15f425eb39428f3d6d983d4 - 158
Application_Registry_Service_DB_2f70cb1bdc274522ac5ca345df9de86e - 4
SharePoint_AdminContent_9cdc3220-ff2c-4b52-abcf-ad9ce1ba463d - 99
WSS_Content - 74
WSS_Content_ac5e33ae4e354dd493b6da176e9e6c84 - 26
WSS_Content_OOTB_upgrade - 46
WSS_Content_portal - 24
wss_content_upgrade - 26
Search_Service_Application_DB_79408a739be74c18ac0b44630382b13c - 99
Managed Metadata Service_c23b61fed6114e88af70931a2add3c36 - 3
managed_metadata_the_truth - 3
User Profile Service Application_ProfileDB_15a709d2085741fb9ed182d6e77e2a4f - 7
WSS_Logging - 1654
User Profile Service Application_SyncDB_9ff01258380945d99a5c9e8e110b6835 - 2
WebAnalyticsServiceApplication_ReportingDB_c7b9b6b2-f3d7-40c4-b72f-70a47e78deec – 6
Total Storage (in MB) = 2,775
They both introduce some new PowerShell learnin' for me. In the first example the new thing was the Label and Expression part at the end. This lets you put something more complicated than just a property in Format-Table. Also lets you put a fancy label on the column. I like it.
The second command took a lot of work. I had to learn a lot of things. I had to learn how to keep a tally as I loop through items. I used the $db variable for that and had to use curly braces to get everything in the right place. I also had to figure out how to write a blank line to separate the Total column. `n did that for me. Finally I wanted to format the output. Disksizerequired is in bytes, which is a gigantic and useless number for us. In both examples I divided it by 1024 twice to get it to megabytes. That wasn't enough. In a large enough farm that value is going to be gigabytes, so I wanted to format it so it could be readable. ("{0:n0}" –f ($db/1024/1024)) made that happen. I did a lot of searching on "Powershell format numbers" to get that all worked out. TechNet had the solution for me. The second script doesn't give as nice of output since we're not using Format-Table anymore. Maybe I'll add that in a later tip.
tk