Limit Maximum Number of Site Collections in SharePoint Content Database using PowerShell

By default, the maximum number of site collections in a SharePoint 2013 content database is set to 5000, and the warning level will be 2000 as initial configurations. Although technically SharePoint 2013 content database can hold up to 10,000 site collections maximum (2,500 non-Personal site collections and 7,500 Personal Sites, or 10,000 Personal Sites alone), 5000 is the recommended limit!

There are times you may want to dedicate a database for a single site collection. When you create a site collection from the central admin, the site is placed automatically in any available content database. To prevent any other sites from being created on the particular content database, We can set the maximum number of sites limit! To set the maximum number of site collections in a specific content database, navigate to:

• SharePoint 2013 Central Administration >> Application Management >> Management Content databases
• Select your target web application in which the particular content database is attached
• Pick the target database from the list
• Now, on the “Manage Content Database Settings” page you can set the maximum number of sites for the content database.

Make sure your maximum number of sites is greater than or equal to the current number of sites.

PowerShell script to set the Maximum number of site collection limit:

This setting should be configured for every content database attached to the particular web application. When you have many content databases, you can use the below PowerShell script to set the max number of sites in a single stretch.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Variables for processing
$WebAppURL ="https://Intranet.Crescent.com"
$MaxSiteCount=1
$WarningSiteCount = 0

#Get all content databases of the web application
$ContentDBColl = Get-SPContentDatabase -webapplication $WebAppURL

#Iterate through each database in the web application
foreach($Database in $ContentDBColl) 
{
    #Check the current No. of sites 
    if($MaxSiteCount -ge $Database.CurrentSiteCount)
    {
        #Set Maximum Sites, warning level Counts
        Set-SPContentDatabase -Identity $Database.Name -MaxSiteCount $MaxSiteCount -WarningSiteCount $WarningSiteCount
        Write-host "Max Sites Settings updated for the database:" $Database.name -ForegroundColor Green
    }
    else
    {
        write-host "MaxSiteCount must be > = current site count! No changes made in $($Database.Name)" -ForegroundColor Red
    }
}

Lockout all existing Content Databases to Limit Site Collections Count

Let’s lock all existing content databases and prevent new site collections creations on them. 

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Variables for processing
$WebAppURL ="https://Intranet.Crescent.com"

#Get all content databases of the web application
$ContentDBColl = Get-SPContentDatabase -webapplication $WebAppURL

#Iterate through each database in the web application
foreach($Database in $ContentDBColl) 
{
    #Get the current Number of sites 
    $Count = $Database.Sites.Count
    if($Count -gt 0)
    {
        $WarniningCount = $Count - 1
        #Set Maximum Sites, warning level Counts
        Set-SPContentDatabase $Database.Name -MaxSiteCount $Count -WarningSiteCount $WarniningCount

        Write-host "Current Number of Sites in $($Database.Name) is $($Database.Sites.Count)"
    }
}