Forecast Content Database Growth

  • Article

One of client requested PowerShell script to forecast content database growth with following requirements:

  • Need to run on SharePoint 2007
  • Output to a CSV file
  • Highlight when a content DB has reached 80GB storage limits
  • Highlight when a content DB has reached 90GB storage limits

In order to complete this script following information was required:

  • Size of each content DB per Web Application
  • Amount of sites per Content DB
  • Average site size per content DB

Perform calculation using all of the above information to determine how many more sites each content DB would allow using the average site size assuming this only goes up to 80GB per content DB.

This was the outcome:

$dTime = Get-Date -Format "yyyyMMdd"
$CSVFile = "C:\PowerShell\SPContentDatabase_$dTime.csv"

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") >null
$WebService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService

$DBName = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty("Name")
$DBSiteCount = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty("CurrentSiteCount")
$DBSize = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty("DiskSizeRequired")

$resultsarray =@()

foreach($WebApplication in $WebService.WebApplications)
{
$ContentDBCollection = $WebApplication.ContentDatabases
$webAppName = $WebApplication.name

               # Enumerate through all content databases attached to the Web application
               foreach($ContentDB in $ContentDBCollection)
               {
                                         
               # Add variables here with any new properties and then add them to the output lines
               $CurrentDBName = $DBName.GetValue($ContentDB, $null)
               $CurrentDBCurrentSiteCount = $DBSiteCount.GetValue($ContentDB, $null)
               $CurrentDBSize = $DBSize.GetValue($ContentDB, $null)
               $ContentDatabaseSizeInMB = $CurrentDBSize/1MB
               $ContentDatabaseSizeInGB = [Math]::Round(($CurrentDBSize/1GB),2)
               $AverageSiteSizeMB = [Math]::Round($ContentDatabaseSizeInMB/$CurrentDBCurrentSiteCount)
               $AverageSiteSizeGB = [Math]::Round(($ContentDatabaseSizeInGB/$CurrentDBCurrentSiteCount),2)
               $DBSpaceLeft = [Math]::Round((90-$ContentDatabaseSizeInGB),2)
               $NewsSitesSpace = $DBSpaceLeft/$AverageSiteSizeGB
              
              
               $DBObject = new-object PSObject
               $DBObject | add-member -membertype NoteProperty -name "WebApplication Name"-Value $webAppName
               $DBObject | add-member -membertype NoteProperty -name "Content DB" -Value $CurrentDBName
               $DBObject | add-member -membertype NoteProperty -name "Number of SitesCollection" -Value $CurrentDBCurrentSiteCount
               $DBObject | add-member -membertype NoteProperty -name "Content DB size in MB" -Value $ContentDatabaseSizeInMB
               $DBObject | add-member -membertype NoteProperty -name "Content DB size in GB" -Value $ContentDatabaseSizeInGB
               $DBObject | add-member -membertype NoteProperty -name "Average Site Size in MB" -Value $AverageSiteSizeMB
               $DBObject | add-member -membertype NoteProperty -name "Average Site Size in GB" -Value $AverageSiteSizeGB
               $DBObject | add-member -membertype NoteProperty -name "Space Left" -Value $DBSpaceLeft
               $DBObject | add-member -membertype NoteProperty -name "Number of Sites To Be Added-GB" -Value $NewsSitesSpace
               $resultsarray += $DBObject                       
               }
}

$resultsarray| Export-csv $CSVFile -notypeinformation