Using PowerShell to Calculate SharePoint database size

Method 1:

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 

Method 2:

#Get SharePoint Content database sizes Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue $date = Get-Date -Format "dd-MM-yyyy" #Variables that you can change to fit your environment $TXTFile = "D:\Reports\SPContentDatabase_$date.txt" $SMTPServer = "yourmailserver" $emailFrom = "SharePointReports@company.com" $emailTo = "youradmin@company.com" $subject = "Content Database size reports" $emailBody = "Daily/Weekly/Monthly report on Content databases" $webapps = Get-SPWebApplication foreach($webapp in $webapps) {     $ContentDatabases = $webapp.ContentDatabases     Add-Content -Path $TXTFile -Value "Content databases for $($webapp.url)"     foreach($ContentDatabase in $ContentDatabases)     {     $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)     Add-Content -Path $TXTFile -Value "-     $($ContentDatabase.Name): $($ContentDatabaseSize)GB"     } } if(!($SMTPServer) -OR !($emailFrom) -OR !($emailTo)) { Write-Host "No e-mail being sent, if you do want to send an e-mail, please enter the values for the following variables: $SMTPServer, $emailFrom and $emailTo." } else { Send-MailMessage -SmtpServer $SMTPServer -From $emailFrom -To $emailTo -Subject $subject -Body $emailBody -Attachment $TXTFile }

The result

Scheduling the script
Save the script (In my example: C:\Install\Get-SPContentDatabaseSize.ps1)
Start the Task scheduler: Start –> Run: taskschd.msc Create a new task:

General

- Make sure the account running the task has sufficient permissions to access the databases
- “Run whether user is logged on or not” should be checked.
- Run with highest privileges has to be checked.
Triggers

The triggers can be set to your requirements, in this example I scheduled the task weekly at 6PM.
Actions To run a PowerShell script as a scheduled task, you cannot simply add the C:\Install\Get-SPContentDatabaseSize.ps1 in the “Program/script” field.
You should do the following:

Program/script: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
Add arguments (optional): -Command C:\Install\Get-SPContentDatabaseSize.ps1