Consolidate SharePoint Content Databases

  • Article

When managing a SharePoint environment, there are times when the number of content databases grow, with most of them only being partially utilized. This could happen as a result of a migration, site collection creations that were not planned properly, or other reasons. This script will help you consolidate databases, showing you which site collections should be moved to which content databases. This script will not modify anything, rather it outputs to a .csv file the results. You can then manually move sites or use a second script to process the csv file.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Summary - This script will analyze all the content databases in your farm and give you
# - output of what sites you need to move to what database in order to free up
# - databases, allowing you to then delete them. It outputs to a .csv file where
# - you can process them manually or use the second script to process.
#
# -
# - This script will not make any changes to your farm.
#-----------------------SETTINGS---------------------------#

#When moving sites, don't let a database grow larger than this number (combined site's usage in GB)
$usageMaxinGB = 150

#When moving sites, don't let a database grow larger than this number (combined site's quota in GB)
$quotaMaxinGB = 400

#When moving sites, don't let a database contain more than this number of sites
$siteCountMax = 20

#Don't move sites from a dataabse that has one or more sites larger than this number (usage in GB)
$maxUsageForProcessingDBinGB = 50

#Will write the overall database consolidation results to this file
$outputFile = "C:\Users\svc.CNT1.setup\Desktop\ContentDBAnalyze\DatabaseOutput.txt"

#Will write specific site collection move results to this file
$SiteMovesOutput = "C:\Users\svc.CNT1.setup\Desktop\ContentDBAnalyze\SiteMovesOutput.txt"
#-----------------------SETTINGS---------------------------#

#Print header for output files
("Database Name~Original Site Count~Original Usage (GB)~Original Quota (GB)~After Site Count~After Usage (GB)~After Quota (GB)~Deleted") | Out-File $outputFile -Append -NoClobber
("Site URL~Site Usage (MB)~Original DB~Moved to DB") | Out-File $SiteMovesOutput -Append -NoClobber

#Loop through each web application to find sites we can shift around
foreach($wa in Get-SPWebApplication)
{
#This hash will contain each DB and their current sum usage and quota size
$hashOfDBValues = @{}
$originalHashValues = @{}

#Loop through each db in the web app
Write-Host ("Processing all content dbs in " + $wa)
foreach($db in $wa.ContentDatabases)
{
$currentUsageCount = 0
$currentQuotaCount = 0

#Loop through each site in the DB
foreach($site in $db.sites)
{
#if 0, something is wrong
if($site.Usage.Storage -eq 0)
{
Write-Host("" + $site.URL + " usage not reporting correctly.")
}
$currentUsageCount += $site.Usage.Storage
$currentQuotaCount += $site.Quota.StorageMaximumLevel
$site.Dispose()
}

$hashOfDBValues[$db.Name] = @{}
$hashOfDBValues[$db.Name]["usageSize"] = $currentUsageCount
$hashOfDBValues[$db.Name]["quotaSize"] = $currentQuotaCount
$hashOfDBValues[$db.Name]["flaggedForDelete"] = $null
$hashOfDBValues[$db.Name]["numberOfSites"] = $db.sites.count

#Store an original copy for future comparisson
$originalHashValues[$db.Name] = @{}
$originalHashValues[$db.Name]["usageSize"] = $currentUsageCount
$originalHashValues[$db.Name]["quotaSize"] = $currentQuotaCount
$originalHashValues[$db.Name]["flaggedForDelete"] = $null
$originalHashValues[$db.Name]["numberOfSites"] = $db.sites.count
}

#Keep looping until there are no more sites that can move around
$noMoreMoves = $false
while($noMoreMoves -eq $false)
{
#Find lowest usage DB that is not flagged to be deleted
$currentLowest = $null
foreach($database in $hashOfDBValues.getEnumerator())
{
#Don't process default DBs or OWA Dbs
if(($database.name -notlike "*default*") -and ($database.name -notlike "*OWA*"))
{
#If it is already flagged to be deleted or has had sites moved to it, skip over it
if($database.Value["flaggedForDelete"] -eq $null)
{
if($currentLowest -eq $null)
{
$currentLowest = $database
}
else
{
if($database.Value["usageSize"] -lt $currentLowest.Value["usageSize"])
{
$currentLowest = $database
}
}
}
}
}

#If currentLowest is $null at this point, the web app doesn't have any more DBs to process
if($currentLowest -eq $null)
{
$noMoreMoves = $true
}
else
{
#Don't process any DBs if over the usage limit variable
if(($currentLowest.Value["usageSize"] / 1GB) -lt $maxUsageForProcessingDBinGB)
{
#Now have current lowest usage db, loop through each site in the DB and find a new home for it
#Flag it for being deleted so we don't process it again
$currentLowest.Value["flaggedForDelete"] = $true
$databaseToMoveSitesFrom = Get-SPContentDatabase $currentLowest.name
$noMoreMoves = $false

foreach($site in $databaseToMoveSitesFrom.Sites)
{
$noMoreMoves = $true
foreach($db in $hashOfDBValues.getEnumerator())
{
#If same DB it is currently in, skip. Also, don't move sites into default or OWA
if(($db.Name -ne $site.ContentDatabase.Name) -and ($db.Name -notlike "*default*") -and ($db.Name -notlike "*OWA*"))
{
#If we already found a home for this DB, move on
if($noMoreMoves -eq $true)
{
#Can it fit usage wise
if((($site.Usage.Storage + $db.Value["usageSize"]) /1GB) -le $usageMaxinGB)
{
#Usage is good, check quota
if((($site.Quota.StorageMaximumLevel + $db.Value["quotaSize"]) /1GB) -le $quotaMaxinGB)
{
#Quota is good too, make sure we aren't already scheduled to delete this db
if($db.Value["flaggedForDelete"] -ne $true)
{
#Don't go over max number of sites per DB
if($db.Value["numberOfSites"] -lt $siteCountMax)
{
#Everything is good, hypothetically "move" site
Write-Host("Moving site " + $site.Url + " from " + $site.ContentDatabase.Name + " to " + $db.Name)
("" + $site.Url + "~" + [System.Math]::Round(($site.Usage.Storage / 1MB), 2) + "~" + $site.ContentDatabase.Name + "~" + $db.Name) | Out-File $SiteMovesOutput -Append -NoClobber

$db.Value["usageSize"] += $site.Usage.Storage
$db.Value["quotaSize"] += $site.Quota.StorageMaximumLevel
$db.Value["numberOfSites"]++
$db.Value["flaggedForDelete"] = $false
$hashOfDBValues[$currentLowest.name].numberOfSites--
$hashOfDBValues[$currentLowest.name].usageSize -= $site.Usage.Storage
$hashOfDBValues[$currentLowest.name].quotaSize -= $site.Quota.StorageMaximumLevel
$noMoreMoves = $false
}
}
}
}
}
}
}

#If there is still some sites left and no more moves available, set the DB to not be deleted
if($noMoreMoves -eq $true)
{
$hashOfDBValues[$currentLowest.name].flaggedForDelete = $false
}
}
}
else
{
$currentLowest.Value["flaggedForDelete"] = $false
}
}

} #End no more moves loop

#Do a before and after to compare what DBs looked like
foreach($database in $originalHashValues.getEnumerator())
{
("" + $database.Name + "~" + $database.Value["numberOfSites"] + "~" + [System.Math]::Round(($database.Value["usageSize"] / 1GB), 2) + "~" + [System.Math]::Round(($database.Value["quotaSize"] / 1GB), 2) + "~" + $hashOfDBValues[$database.name].numberOfSites + "~" + [System.Math]::Round(($hashOfDBValues[$database.name].usageSize / 1GB), 2) + "~" + [System.Math]::Round(($hashOfDBValues[$database.name].quotaSize / 1GB), 2) + "~" + $hashOfDBValues[$database.name].flaggedForDelete) | Out-File $outputFile -Append -NoClobber
}
}