About Me

My photo
Muthupet, TamilNadu, India
SharePoint 2010

Tuesday, February 4, 2014

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
image
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:
image
General
image
- 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
image

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:
image
Program/script: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
Add arguments (optional): -Command C:\Install\Get-SPContentDatabaseSize.ps1
 

No comments:

Post a Comment